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

For SQL Server 2005, check out Erland Sommarskog's excellent Arrays and Lists in SQL Server 2005 article which shows some techniques how to deal with lists and arrays in SQL Server 2005 (he also has another article for SQL Server 2000).

If you could upgrade to SQL Server 2008, you can use the new feature called "table valued parameter":

First, create a user-defined table type

CREATE TYPE dbo.MyUserIDs AS TABLE (UserID INT NOT NULL)

Secondly, use that table type in your stored procedure as a parameter:

CREATE PROC proc_GetUsers @UserIDTable MyUserIDs READONLY 
AS
SELECT * FROM dbo.Users
    WHERE userid IN (SELECT UserID FROM @UserIDTable)

See details here.

Marc

Just use it like this will work

Create procedure sp_DoctorList 
@userid varchar(100)
as 
begin
exec ('select * from doctor where userid in ( '+ @userid +' )')
end

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