SQL : in clause in stored procedure:how to pass va

Question

I want to write a SQL Server 2005 stored procedure which will select and return the user records from the user table for some userids which are passed to the stored procedure as parameter.

How to do this ?

I can pass the user ids as a string separated by comma. So that I can use the

select * 
from users 
where userid in (userids)

E.g. : I want to select records for id's 5,6,7,8,9

How to write the stored procedure ?

Answer 1

you could use dynamic sql. Pass the in statement to a Sql SP via a variable and concatenate it into a query in the SQL and execute using sp_execute sql

create procedure myproc(@clause varchar(100)) as 
begin
  exec sp_executesql 'select * from users where userid in ( ' + @clause +' )'
end