Using 'IN' query in Stored Procedure

[Auld Nick II]

I'm trying to develop a sproc that does the following type of search.


Select * from table1 where id in (101, 102, 103)

Obviously I don't want to hardcode values in so I would rather have

create procedure GetListofValues
(@ID varchar(500))
as
select * from table1 where id in (@ID)


but of course I can't use this because the column I'm searching on is an integer columns (it's an identity column so I can't change it) and I can't pass it in as an integer because of the comma separation of various different numbers.

Does anyone have any ideas?

Cheers.

AuldNick

[brucevde]

You need to create/execute a dynamic sql string.

create procedure GetListofValues
(@ID varchar(500)) 
as
declare @SQL varchar(2000)

Set @SQL = 'select * from table1 where id in (' + @ID + ')'

exec sp_executesql @sql

[Auld Nick II]

That's exactly what I needed. Thanks Brucevde.