Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

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.


Last Update:






About the author





More SQL Server Solutions











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 


SQL tips:

*Enter Code refresh code     



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

 

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

pls


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

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.

 

 


Learn more about SQL Server tools