Running a Dynamic Query against SQL Server without using Dynamic SQL
August 27th 2008 04:37
Running a Dynamic Query against SQL Server without using Dynamic SQL
Problem
I am trying to pass a comma delimited list of values into a stored procedure to limit the result set. Whenever I use the variable in the IN clause I get an error message. Is there a way to do this without using Dynamic SQL?
Solution
There is a way to do this without using Dynamic SQL, but first lets explore the problem. I will be using the AdventureWorks Database in the following examples.
This will work great as long as you only have a single value.
Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3'
Select * from HumanResources.Employee
Where ManagerID IN (@ManagerIDs)
But as soon as you add the comma, the results will look something like this.
Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3,6'
Select * from HumanResources.Employee
Where ManagerID IN (@ManagerIDs)
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '3,6' to data type int.
This is because SQL Server knows that the ManagerID column is an integer and is trying to implicitly convert the @ManagerIDs variable.
In order to resolve the issue you can execute the statement using Dynamic SQL. This will allow you to build the entire query “dynamically” before executing it.
Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3,6'
Declare @SQL Varchar(1000)
Set @SQL =
'Select * from HumanResources.Employee
Where ManagerID IN (' @ManagerIDs ')'
EXEC (@SQL)
This will allow you to execute the query, but Dynamic SQL is a security risk and may not even be allowed in certain organizations.
So how do you execute the query without using Dynamic SQL? This can be accomplished using XML.
The first thing you need to do is create an xml string from the comma delimited string.
Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3,6'
DECLARE @XmlStr XML
SET @XmlStr =
--Start Tag
'<ManagerID>'
--Replace all commas with an ending tag and start a new tag
REPLACE( @ManagerIDs, ',', '</ManagerID><ManagerID>')
--End Tag
'</ManagerID>'
Selecting the xml value will display the following.
Select @XmlStr
1. < managerid >3</ managerid > < managerid >6</ managerid >
Now that you have an xml string we can query it and display the results as rows.
SELECT x.ManagerID.value('.', 'INT') AS A
FROM @XmlStr.nodes('//ManagerID') x(ManagerID)
A
1. 3
2. 6
Now you can use the previous query to limit the results.
SELECT *
FROM HumanResources.Employee
WHERE ManagerID IN(
SELECT x.ManagerID.value('.', 'INT') AS A
FROM @XmlStr.nodes('//ManagerID') x(ManagerID)
)
Or you can limit the results by using an Inner Join.
SELECT *
FROM HumanResources.Employee AS A
INNER JOIN
(SELECT x.ManagerID.value('.', 'INT') AS ManagerID
FROM @XmlStr.nodes('//ManagerID') x(ManagerID)) B
ON A.ManagerID = B.ManagerID
Problem
I am trying to pass a comma delimited list of values into a stored procedure to limit the result set. Whenever I use the variable in the IN clause I get an error message. Is there a way to do this without using Dynamic SQL?
Solution
There is a way to do this without using Dynamic SQL, but first lets explore the problem. I will be using the AdventureWorks Database in the following examples.
This will work great as long as you only have a single value.
Declare @ManagerIDs Varchar(100)
Select * from HumanResources.Employee
Where ManagerID IN (@ManagerIDs)
But as soon as you add the comma, the results will look something like this.
Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3,6'
Select * from HumanResources.Employee
Where ManagerID IN (@ManagerIDs)
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '3,6' to data type int.
This is because SQL Server knows that the ManagerID column is an integer and is trying to implicitly convert the @ManagerIDs variable.
In order to resolve the issue you can execute the statement using Dynamic SQL. This will allow you to build the entire query “dynamically” before executing it.
Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3,6'
Declare @SQL Varchar(1000)
Set @SQL =
'Select * from HumanResources.Employee
Where ManagerID IN (' @ManagerIDs ')'
EXEC (@SQL)
This will allow you to execute the query, but Dynamic SQL is a security risk and may not even be allowed in certain organizations.
The first thing you need to do is create an xml string from the comma delimited string.
Declare @ManagerIDs Varchar(100)
Set @ManagerIDs = '3,6'
DECLARE @XmlStr XML
SET @XmlStr =
--Start Tag
'<ManagerID>'
--Replace all commas with an ending tag and start a new tag
REPLACE( @ManagerIDs, ',', '</ManagerID><ManagerID>')
--End Tag
'</ManagerID>'
Selecting the xml value will display the following.
Select @XmlStr
1. < managerid >3</ managerid > < managerid >6</ managerid >
Now that you have an xml string we can query it and display the results as rows.
SELECT x.ManagerID.value('.', 'INT') AS A
FROM @XmlStr.nodes('//ManagerID') x(ManagerID)
A
1. 3
2. 6
Now you can use the previous query to limit the results.
SELECT *
FROM HumanResources.Employee
WHERE ManagerID IN(
SELECT x.ManagerID.value('.', 'INT') AS A
FROM @XmlStr.nodes('//ManagerID') x(ManagerID)
)
Or you can limit the results by using an Inner Join.
SELECT *
FROM HumanResources.Employee AS A
INNER JOIN
(SELECT x.ManagerID.value('.', 'INT') AS ManagerID
FROM @XmlStr.nodes('//ManagerID') x(ManagerID)) B
ON A.ManagerID = B.ManagerID
| 63 |
| Vote |
Add To: 
Subscribe to this blog
Comment by TimmyH
Tech News
Can you HACK it?
Genyration