Monday, July 7, 2008

Dynamic IN Clause using XML (no dynamic sql)

A lot of times people want to pass a variable to a stored procedure that contains a list of delimited values and use it in the IN clause of a query. For example...


DECLARE @String VARCHAR(MAX)

SET @String = '1,2'

SELECT FROM WHERE IN (@String)


The problem is you normally can't do this unless you use dynamic SQL to build the string first and then execute the code like this...


DECLARE @String VARCHAR(MAX)
SET @String = '1,2'
DECLARE @SQL VARCHAR(1000)

SET @SQL = 'SELECT * FROM TableName WHERE ColumnName IN (' + @String + ')'
EXEC (@SQL)


Many companies have policies against dynamic SQL, so you can actually use XML to accomplish the same task.



DECLARE @t TABLE(
col1 INT IDENTITY(1,1),
col2 CHAR(1))

INSERT INTO @t (col2) VALUES ('a')
INSERT INTO @t (col2) VALUES ('b')
INSERT INTO @t (col2) VALUES ('c')

DECLARE @String VARCHAR(MAX)

--This would be the variable passed to a sp for example
SET @String = '1,2'



--THIS WILL PARSE AN XML STRING WITH A COMMA DELMITER
DECLARE @x XML
SET @x = '<j>' + REPLACE( @String, ',', '</j><j>') + '</j>'
--Select @x


--Now you can use the xml to Join on a talbe as in EXAMPLE 1

--EXAMPLE 1
SELECT *
FROM @t t1
INNER JOIN(
SELECT x.j.value('.', 'INT') AS col1
FROM @x.nodes('//j') x(j)
)
AS t2
ON t1.col1 = t2.col1




--Or you can use the xml in an in clause as in EXAMPLE 2
--EXAMPLE 2
SELECT *
FROM @t t1
WHERE col1 IN(
SELECT x.j.value('.', 'INT') AS col1
FROM @x.nodes('//j') x(j)
)


0 comments: