Run a Dynamic Query against SQL Server without Dynamic SQL

By:   |   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

XMLResultSet

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)

XMLFinalValues

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ken Simmons 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



Comments For This Article




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

 

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

pls


Tuesday, May 8, 2012 - 2:29:49 PM - Linda Leslie Back To Top (17344)

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 (1704)

HI,

 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.

Regards,

Mayur Shendge.

 

 















get free sql tips
agree to terms