Search code examples
sqlsql-serversql-server-2005stored-procedures

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

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 ?

Solution

  • 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