IN keyword in Dynamic SQL

Question

I have a stored procedure which is implemented in Dynamic SQL. I am trying to include an IN keyword and pass fixed values in the list between single quotes.

I want to write something like this:

where grp.Status in ('A,S') 

but the above statement is already encapsulated in single quotes.

I tried using something like the one below:

where grp.Status in (' +QUOTENAME('A,S','''')+  

but the query is only recognizing the first value in the list i.e. 'A'.

I also read about using a split function and putting the values in a temp table and using the temp table's column instead. But, I don't was to do that process for a tiny list.

Any suggestions and solutions are greatly appreciated.

Answer 1

You can just put the list into the SQL:

'where grp.Status in (''' + replace('A,S', ',', ''',''') + ''') . . . 

If I got those single quotes right, this should produce the result as:

where grp.Status in ('A','S') . . .

Answer 2

If you do not want to use the split function you can do the following with your dynamic sql.

declare @xml xml 
      , @list Varchar(100) = 'A,B'

set @xml = N'<root><r>' + replace(@list, ',' ,'</r><r>') + '</r></root>'

Declare @Sql Nvarchar(MAX);

SET @Sql = N'Select * from TableName grp
             WHERE grp.Status IN (
                                   select r.value(''.'',''varchar(max)'') as item
                                  from @xml.nodes(''//root/r'') as records(r)
                                  )'

Exec sp_executesql @Sql
                  ,N'@xml XML'
                  ,@xml