SQL: Passing a parameter for an IN clause.

[~J~]

Anyone know an answer to this?

Got a SELECT statement which uses an IN clause to determine what rows to retrieve.

EG. SELECT name FROM emps WHERE uid IN (10,12,14)

What I'm trying to do however is create a parameter, pass in a comma seperated list and then use this as the IN clause.

EG.
DECLARE @emps NVARCHAR(30)
SET @emps ='10,12,33'
SELECT name FROM emps WHERE uid IN (@emps)

Now this is failing, and I know why it's failing because the parameter is of nvarchar type yet the uid is of int.

I can't change the @emps parameter to int, because I then can't have a comma-seperated list!

Any thoughts on how I may be able to do this?

 

[GravyMonster]

I had this problem a while back. The only workaround I found was to actually build the SQL command text in the SP itself...

DECLARE @In_List nvarchar(256)
DECLARE @SQL nvarchar(4000)

SET @In_List = '12,13,15'

SET @SQL = 'SELECT [Field] FROM [Table] WHERE [ID_Field] IN
(' + @In_List + ')'

CALL sp_executesql @SQL

HTH!

 

[~J~]

Hmmm good call, didn't think of that!

Cheers spunkey.