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.

Gordon Linoff · Accepted Answer · 2016-12-21 21:43:40Z

2

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') . . .

answered Dec 21, 2016 at 21:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

levelonehuman Over a year ago

Our database has something similar to handle things like this, but good lord does it turn into an unreadable mess quickly.

HLGEM Over a year ago

@levelonehuman, that's why the use of the split function and then joining to a table is much easier to maintain.

M.Ali · Accepted Answer · 2016-12-21 21:51:23Z

0

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

answered Dec 21, 2016 at 21:51

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Collectives™ on Stack Overflow

IN keyword in Dynamic SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related