I have been using IN clause with multiple values from a long time. But I was always using it with string concatenation along with single quotes to make it string and then process it.
Recently, I found another solution , it works without string concatenation.
Let me explain this issue demonstrating simple example.
CREATE TABLE [dbo].[Student]( [Student ID] [varchar] (6) Not NULL , [Student Name] [varchar](50) NOT NULL) GO Insert into dbo.[Student] values ('STD001','Bob') Insert into dbo.[Student] values ('STD002','Alexander') Insert into dbo.[Student] values ('STD003','Hosanna') Insert into dbo.[Student] values ('STD004','William') Insert into dbo.[Student] values ('STD005','Hulda') Insert into dbo.[Student] values ('STD006','Jacoba')
Old Approach :
Declare @SQL VARCHAR(MAX) Declare @WhereClause VARCHAR(MAX) Set @WhereClause='Bob,Hulda,Jacoba' SET @WhereClause=REPLACE(@WhereClause,',',''',''') Set @SQL='Select * from dbo.[Student] Where [Student Name] In (''' + @WhereClause + ''')' EXEC (@SQL)
New Approach :
Declare @Xml AS XML Declare @WhereClause VARCHAR(MAX) Set @WhereClause='Bob,Hulda,Jacoba' SET @Xml = cast(('<A>'+replace(@WhereClause,',' ,'</A><A>')+'</A>') AS XML) Select * from dbo.[Student] Where [Student Name] In ( SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A) )
Explanation :
In the new approach, I need to explain these two important lines of code.
SET @Xml = cast(('<A>'+replace(@WhereClause,',' ,'</A><A>')+'</A>') AS XML) Select @Xml
This code snippet will convert string into XML, replacing the delimiter with start and end XML tag.
(Here, It is used as <A></A>).
Output :
SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)
Once the string is converted into XML,By using this code snippet, we can easily query it via xquery.
Output :
I’d really appreciate your comments on my posts, whether you agree or not, do comment.
Very clever solution to a vexing problem. Thank you. It works well with storage procedures queried via Entity Framework.
Fabulous. Worked like a charm the very first time. Thank you!!
Ingenious! Appreciate your sharing.
excellent, That is what I was looking for
Thank you again
very usefull.thanks
Awesome…worked like a charm. I appreciate!!!!!!!!!!Thanks.
Hi Imran, I implemented the above approach. But i was thinking about the performance here. i see a difference when i execute the query in older approach and new one. The new approach with XML is taking 5 times more than the older one. So is it good to use the new approach still ?
Thanks, Jayashree.
Hi Jayashree,
Thank you for your feedback. You are absolutely right because I tested the old and new approach and find some performance issues. Given below are the execution time for old and new approach.
–Old Approach
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 8547 ms.
–New Approach
SQL Server Execution Times:
CPU time = 1092 ms, elapsed time = 8862 ms.
But, there is a reason to design new approach and the reason is, we cannot convert the complete stored procedure into a varchar variable and execute it just because of one “where clause”.
And In the new approach you don’t need to assign it into a variable.
Imran
Hi
Assalamualaikum,
Great job! two thumbs up!
Indonesia says thanks to you…
[…] my earlier article, I developed a solution How to use multiple values for IN clause using same parameter (SQL Server). Recently, I received a query, inquiring how to pass multiple values through one parameter in a […]
Hi Imran, Thanks a lot for sharing this wonderful solution. Great Job. In my case I cant use dynamic sql as I’m inserting into temporary table. Your solution helped me to solve the issue.
[…] function now was to construct a SELECT … WHERE … IN … @variable Google found this example : How to use multiple values for IN clause using same parameter (SQL Server) | SQL Server Portal Here is what I constructed around that: […]
Perfect 🙂 Works like a charm
I have a & symbol in @WhereClause. how to make it work.
[…] How to use multiple values for IN … – SQL Server Portal – How to use multiple values for IN clause using same parameter (SQL Server) December 21, 2011 by Muhammad Imran […]
Really good solution. Thanks 🙂