Using parametrized "IN"

[mironline]

dear friends


I have a problem with using "WHERE * IN ( ...... )" in sqlcommand ;

for exp;

select * from users where id in (1,2) 
// return 2 rows

but occur error when pass the "1,2" with parameters

declare @id nvarchar(3)
set @id = '1,2'
select * from users where id in (@id)

please help me to find good solution .

thanks

[ck9663]

Where does your "1,2" value comes from? If it's from a front-end, trying building your query dynamically and use an OR instead.

--- CK

[east7]

hi,

try this

--
USE tempdb
GO

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'testSP' AND type = 'P')
DROP PROCEDURE testSP

GO

CREATE PROCEDURE testSP
(
@ids varchar(20)
)
AS
BEGIN

DECLARE @prc nvarchar(300)

SET @prc = 'SELECT * FROM Delta d WHERE d.ID IN (' + @ids + ')'

EXEC(@prc)
END
GO

EXEC testSP '1,2'
GO
--

thanks
east7