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.