How can I use WHERE id IN (@a_varchar_variable) in SQL Server stored procedures?

Question

In my table id column's datatype is int.

And I have a list of items' ids to be selected.

So, here is some piece of my code:

DECLARE @user_list
SELECT @user_list=(
SELECT user_list
FROM sometable
WHERE rowNum=xxx
);
SELECT *
FROM anotherTable
WHERE user_id IN(@user_list)

but it doesn't work, the error msg like :

can't convert varchar to int

Thanks!

The `WHERE id IN (...)` expects a **list of (several) `int`** values, while you're passing in a **single string** that contains a list of int... that is not the same thing and they're not compatible. You need to **extract** those ints from the string and pass them in as a list of `int` — marc_s, Aug 20 '12 at 08:13

score 7 · Accepted Answer · answered Aug 20 '12 at 08:14

You can Create a Split function and that will be use when you want.

CREATE FUNCTION [dbo].[Split]
(   
    @List nvarchar(max),
    @SplitOn nvarchar(1)
)
RETURNS @RtnValue table (
    Id int identity(1,1),
    Value nvarchar(max)
)
AS
BEGIN
    While (Charindex(@SplitOn,@List)>0)
    Begin 
        Insert Into @RtnValue (value)
        Select 
            Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
        Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
    End 

    Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    Return
END

after that you can call the function in your query as below.

SELECT * FROM anotherTable WHERE user_id IN(dbo.split(@user_list,','))

Otherwise, you have to use dynamic query.

SELECT * FROM anotherTable WHERE user_id IN(SELECT Value FROM dbo.split(@user_list,',')) is right! Thanks! — MangMang, Aug 20 '12 at 08:36

Joe G Joseph · Answer 2 · 2012-08-20T08:22:59.483

0

you can use dynamic sql

exec('
SELECT *
FROM anotherTable
WHERE user_id IN('+@user_list+')')

edited Aug 20 '12 at 08:22

answered Aug 20 '12 at 08:05

Joe G Joseph

23,518
5
56
58

podiluska · Answer 3 · 2012-08-20T08:16:24.257

0

If you make @user_list a table variable, you can join or filter against it like any other table

 declare @user_list table (id int)
 insert @user_list(id)
 SELECT user_list 
 FROM sometable 
 WHERE rowNum=xxx 


 SELECT * 
 FROM anotherTable 
 WHERE user_id IN(select Id from @user_list)

edited Aug 20 '12 at 08:16

answered Aug 20 '12 at 08:07

podiluska

50,950
7
98
104

score 0 · Answer 4 · answered Aug 20 '12 at 08:08

Just use a table variable, which allows you to add normal word into a variable:

DECLARE @user_list AS TABLE (user_id int NOT NULL);

INSERT @user_list
SELECT user_list
FROM sometable
WHERE rowNum=xxx;

SELECT *
FROM anotherTable
WHERE user_id IN (SELECT user_id FROM @user_list)

How can I use WHERE id IN (@a_varchar_variable) in SQL Server stored procedures?

4 Answers4