By: Ken Simmons | Updated: 2008-08-26 | Comments (3) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > 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 |
|
|
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) |
|
|
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 |
Next Steps
- I seem to be integrating XML more and more into routine tasks. I recommend at least having a basic knowledge of XML as it seems to be a more powerful tool with each release of SQL Server.
- Check out more MSSQLTips related to XML.
About the author
Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2008-08-26
