Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this tSQL code which works OK:

SELECT 
c.logguid,
a.b.value('./PropertyValue', 'varchar(max)') asd
  FROM [dnn].[dbo].[EventLog2] c

cross apply sss.nodes('/LogProperties/LogProperty[PropertyName=sql:variable("@x") and PropertyValue=sql:variable("@y")]') as a(b)

However, what I want to be able to do is pass in a dynamic list of many pairs of values which are OR'd between each one, i.e.

SELECT 
c.logguid,
a.b.value('./PropertyValue', 'varchar(max)') asd
  FROM [dnn].[dbo].[EventLog2] c

cross apply sss.nodes(
'/LogProperties/LogProperty[PropertyName=sql:variable("@x") and PropertyValue=sql:variable("@y")
or
PropertyName=sql:variable("@a") and PropertyValue=sql:variable("@b")
]'
) as a(b)

Is there a way of doing this?

share|improve this question
1  
To make sure I understand, there could also be @c and @d etc? –  wtjones Sep 4 '12 at 22:15
add comment

1 Answer

Any parameter that needs a variable list or array is probably a good candidate for a user-defined table type. I would create the type as:

CREATE TYPE [PropertyVariableTableType] AS TABLE (
    PropertyName nvarchar(255),
    PropertyValue nvarchar(255) )

Table types can be used as parameters to stored procedures just as any other type. You can then either join on the user-defined table or iterate the rows to dynamically build the query.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.