Variable for element name

Question

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?

To make sure I understand, there could also be @c and @d etc? — wtjones
– wtjones, Commented Sep 4, 2012 at 22:15

dartonw · Accepted Answer · 2012-12-04 14:46:42Z

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.

Stack Exchange Network

Variable for element name

1 Answer 1

Hot Network Questions

Variable for element name

1 Answer 1

Related

Hot Network Questions