Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Run a Dynamic Query against SQL Server without Dynamic SQL

By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | 3 | 4 | More > Dynamic SQL

Fix SQL Server Performance Issues - Free Webcast

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?

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 + ')'


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'

SET @XmlStr =
--Start Tag
'<ManagerID>' +
--Replace all commas with an ending tag and start a new tag
REPLACE( @ManagerIDs, ',', '</ManagerID><ManagerID>') +
--End Tag

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.

FROM HumanResources.Employee
SELECT x.ManagerID.value('.', 'INT') AS A
FROM @XmlStr.nodes('//ManagerID') x(ManagerID)

Or you can limit the results by using an Inner Join.

FROM HumanResources.Employee AS A
(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.

Last Update:

next webcast button

next tip button

About the author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Send me SQL tips:


Saturday, May 12, 2012 - 11:55:42 AM - sureshmnsamy Back To Top


its very interesting this query ... but i need sub query 


Tuesday, May 08, 2012 - 2:29:49 PM - Linda Leslie Back To Top

there are lots of versions of the SPLIT function as well that can be used too

Tuesday, August 26, 2008 - 10:02:08 PM - MAYUR Back To Top


 I would like to thank you for publishing such a wonderfull article. its really helpful to me.

i would like to know more about the XML. i wants to start from very basic.As i am using MSsql form last year, i can get  TSQL but, i want to become handy for the XML in T-SQL.

Please suggest some good articles and books.

once again thanks for your great article.

Thanking you.


Mayur Shendge.



Learn more about SQL Server tools