I have this tSQL code which works OK:

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.

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")
PropertyName=sql:variable("@a") and PropertyValue=sql:variable("@b")
) as a(b)

Is there a way of doing this?

  • 1
    To make sure I understand, there could also be @c and @d etc? – wtjones Sep 4 '12 at 22:15

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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