How to implement parameterized SQL if the variable is inside an IN clause in the T-SQL statement

Question

I have something as mentioned below

declare @param1 varchar(20), var1 varchar(20), var2 varchar(20), 
        @sql nvarchar(max), @var3 varchar(20)

set @var1 = 'value1'
set @var2 = 'value2'
set @var3 = 'value3'

set @sql = '....... where name in (' + @var2 +') and size in (@param1) 
and class in ('+ @var3 + ')'

execute sp_executesql @sql, N'@param1 varchar(20), @var2 varchar(20), 
@var3 varchar(20)', @param1 = @var1, @var2 = @var2, @var3 = @var3;

The query is not getting executed. Am I doing something wrong? If yes, then what is the correct way to do this?

[Parameterize an SQL IN clause](http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — Lukasz Szozda, Dec 30 '18 at 18:14
Sir, for all of you saying this is similar to the linked question, it is not. Maybe I have not been able to explain my query well. When I print my variable @param1, I get 'val1','val2' By not getting executed, I mean, the query is not executing and producing any results — Himanshu Jindal, Dec 30 '18 at 19:02

score 0 · Accepted Answer · answered Dec 30 '18 at 18:16

0

You cannot pass a list as a single parameter. Instead, use string_split() or a similar function:

set @sql = '.......
where name in (select string_split(@param1, '','')) and
      size in (select string_split(@param2, '','')) and
      class in (select string_split(@param3, '',''))
';

execute sp_executesql @sql,
                      N'@param1 varchar(20), @param2 varchar(20), @param3 varchar(20)',
                      @param1 = @var1, @param2 = @var2, @param3 = @var3;

Or, if you are just passing one value, use =:

set @sql = '.......
where name = @param1 and
      size = @param2 and
      class = @param3
';

execute sp_executesql @sql,
                      N'@param1 varchar(20), @param2 varchar(20), @param3 varchar(20)',
                      @param1 = @var1, @var2 = @var2, @var3 = @var3;

Note that you need to be careful about the naming of the parameters. I typically use the same names in the dynamic SQL:

set @sql = '.......
where name = @param1 and
      size = @param2 and
      class = @param3
';

execute sp_executesql @sql,
                      N'@var1 varchar(20), @var2 varchar(20), @var3 varchar(20)',
                      @var1=@var1, @var2=@var2, @var3=@var3;

answered Dec 30 '18 at 18:16

Gordon Linoff

1,242,037
58
646
786

If my list is a varchar(2000) which is comma separated string, then isn't that what the I clause expects. For ex - ..... where name in ('Hi','Bye','Nope') This is what IN clause expects, then why should I split my string parameter? – Himanshu Jindal Dec 30 '18 at 18:41
@HimanshuJindal . . . because `('a,b,c')` is not the same as `('a', 'b', 'c')`. The first is a list with one string (that happens to have commas). The second is a list with three strings. – Gordon Linoff Dec 30 '18 at 19:00
My variable is the latter case. It is a list stored in a varchar variable and in the second form as mentioned above by you. Finally, the value of my variable at the time of forming the sql statement is 'a','b','c' – Himanshu Jindal Dec 30 '18 at 19:03
@HimanshuJindal . . . That doesn't matter. Use a split function! – Gordon Linoff Dec 30 '18 at 19:13
Can you please an example of the correct split function. The split function you mentioned isn't working – Himanshu Jindal Dec 30 '18 at 19:15
@HimanshuJindal look up Jeff moden sql splitter. He has a great one. – S3S Dec 31 '18 at 02:13

How to implement parameterized SQL if the variable is inside an IN clause in the T-SQL statement

1 Answers1