Storing Where IN values in a variable

Question

I want to store a comma separated list in a variable, and use that data in this manner, however it doesn't seem to work.It returns the following error:

Conversion failed when converting the nvarchar value '2, 4, 7, 9' to data type smallint.

I have written the following query :

declare @sch nvarchar(255)
set @sch = '2, 4, 7, 9'

SELECT *
FROM stu
WHERE sc IN (@sch);

Thank you.

Basically, you just want to [parametrise an IN clause](http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause). — Andriy M, Apr 17 '15 at 05:38

score 1 · Accepted Answer · edited May 23 '17 at 10:26

I found some info in another post on StackOverflow here that gave what I think is a good example on how to do this. It has a minimal amount of code, and also doesn't store the SQL in a variable (which I didn't want). I think it can even be less code if CONVERT(varchar(max),SC) was stored in a variable...but not sure how to do that as part of the WHERE clause.

declare @sch nvarchar(255)
set @sch = '2, 4 , 7,9,' -- can have spaces or not, or comma at end.
set @sch = REPLACE(@sch,' ','') -- strips out any spaces

SELECT * FROM stu
WHERE @sch LIKE '%,'+CONVERT(varchar(max),SC)+',%'
    OR @sch LIKE '%,'+CONVERT(varchar(max),SC)
    OR @sch LIKE CONVERT(varchar(max),SC)+',%'
    OR @sch=CONVERT(varchar(max),SC);

score 0 · Answer 2 · answered Apr 12 '15 at 06:10

0

Use this query:

declare @sch nvarchar(255)
set @sch = '2, 4, 7, 9'

declare @query varchar(4000)= 
'
SELECT *
FROM 
(
    select 2 as sc
    union all
    select 1 as sc
    union all
    select 7 as sc
) as stu
WHERE sc IN ('+@sch+');'

exec (@query)

Just change my subquery to real table

answered Apr 12 '15 at 06:10

Vladimir Semashkin

1,270
1
10
21

I would prefer not to store the query in a variable like your example. Perhaps there is another way to do it? – user3513237 Apr 12 '15 at 06:15
While it will work, it's usually not recommended to use dynamic sql like this. it's the most Vulnerable to sql injection attacks, for one reason. Imagine what will happen if the value of @sch was '1,2,4,7,9) or (1=1' – Zohar Peled Apr 12 '15 at 06:16
This is a good way for such situation. You spend far more resources if you wil try to split your string. Dynamic sql - very useful but you need to think when use it. This situation is a good reason to use it. – Vladimir Semashkin Apr 12 '15 at 06:19
@VladimirSemashkin: If you read my answer, you will see I specifically said it's not always the best choice to split a string in sql, and that there are better ways to pass a list of parameters to sql. I don't agree that your suggestion is a good solution for such situations, since clearly the situation is NOT what the OP wrote. if it was, the correct answer would be **SELECT * FROM stu WHERE sc IN (2,4,7,9);**. it's probably a question on how to pass a list of values to a stored procedure, and for that there are better ways then dynamic sql. – Zohar Peled Apr 12 '15 at 06:55

score 0 · Answer 3 · answered Apr 12 '15 at 07:30

@user3513237 Use the below code

declare @sch nvarchar(255)
declare @sch1 nvarchar(255)
declare @records table(a int)
declare @lenSch int
declare @i int =1
set @sch = '2,4,7,9'
print @sch
set @lenSch=(select len(@sch))
while(@i<=@lensch)
begin
insert  into @records
select SUBSTRING(@sch,@i,1)
set @i=@i+2
end
SELECT *
FROM stu
WHERE sc IN (select a from @records)

score 0 · Answer 4 · answered Apr 12 '15 at 08:57

You can use following function to split string and after that you can query this function as bellow:

CREATE FUNCTION [dbo].[SDF_SplitString]
(
@sString nvarchar(2048),
@cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
if @sString is null return
declare @iStart int,
        @iPos int
if substring( @sString, 1, 1 ) = @cDelimiter 
begin
    set @iStart = 2
    insert into @tParts
    values( null )
end
else 
    set @iStart = 1
while 1=1
begin
    set @iPos = charindex( @cDelimiter, @sString, @iStart )
    if @iPos = 0
        set @iPos = len( @sString )+1
    if @iPos - @iStart > 0          
        insert into @tParts
        values  ( substring( @sString, @iStart, @iPos-@iStart ))
    else
        insert into @tParts
        values( null )
    set @iStart = @iPos+1
    if @iStart > len( @sString ) 
        break
end
RETURN
END

After adding this function to your database execute your query as below:

DECLARE @sch NVARCHAR(255)
SET @sch = '2, 4, 7, 9'

SELECT  *
        FROM    stu
        WHERE   sc IN ( SELECT  part
                                FROM    dbo.SDF_SplitString(@sch, ',') );

score 0 · Answer 5 · answered Apr 12 '15 at 11:08

0

Here is the query that will solve your problem:

declare @sch nvarchar(255);
declare @query varchar(200);
set @sch = '2, 4, 7, 9';
set @query = 'select * from stu where sc in (' + @sch + ')';
--print @query;
exec(@query);

answered Apr 12 '15 at 11:08

Mayuran Parathalingam

41
8

score 0 · Answer 6 · answered Apr 14 '15 at 01:50

Whenever possible, you want to avoid using cursors or loops as SQL is not optimized for those. Here's a recursive solution.

Your Variables

DECLARE @stu TABLE (col1 INT);
INSERT INTO @stu VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

DECLARE @sch nvarchar(255);
SET @sch = '2,4,7,9';
SET @sch = REPLACE(@sch,' ','') + ','; -- Put the end comma there instead of having to use a case statement in my query
                                       -- As well as getting rid of useless white space with REPLACE()

Actual Query

WITH CTE
AS
(
    SELECT 1 row_count, CAST(SUBSTRING(@sch,0,CHARINDEX(N',',@sch,0)) AS NVARCHAR(255)) AS search_val, CHARINDEX(',',@sch,0) + 1 AS starting_position
    UNION ALL
    SELECT row_count + 1,CAST(SUBSTRING(@sch,starting_position,CHARINDEX(',',@sch,starting_position) - starting_position) AS NVARCHAR(255)) AS search_val, CHARINDEX(',',@sch,starting_position) + 1 AS starting_position
    FROM CTE
    WHERE row_count < (LEN(@sch) - LEN(REPLACE(@sch,',','')))
)

SELECT *
FROM @stu
WHERE col1 IN (SELECT CAST(search_val AS INT) FROM CTE)

Results:

col1
-----------
2
4
7
9

Storing Where IN values in a variable

6 Answers6

Your Variables

Actual Query