This Stored Procedure shows how to accept a comma delimited list of ID’s and return records from a table that match the items of the delimited list:
-- ============================================= -- Description: Returns one or more user records from the Users table. -- This sproc allows clients to have to make only one call to retrieve more than one record from a table (as opposed to calling the sproc for each record). -- The parameter is a varchar that can be a comma delimited list of UserId's. ---- For example: A parameter of '1,8,9,45' will return four users of which have a UserId ---- of 1, 8, 9, or 45 -- ============================================= ALTER PROCEDURE [dbo].[GetUsers] -- Declare the SP Parameter. @UserIdList Varchar(MAX) --The UserIdList is a comma delimited list of UserId's AS --Create a var that defines the char that is used for the UserIdList delimiter --We need to do this because a list such as (1,2,4,99) returns true in the T-SQL IsNumeric function - which screws up the IsNumeric condition later on in this SP DECLARE @ListDelimiter VARCHAR(1) SET @ListDelimiter = ',' --Remove white spaces from the parameter string SET @UserIdList=REPLACE(@UserIdList,' ','') --Create the temporary table that will hold rows of ID's that were passed in the parameter CREATE TABLE #tmpUserList (UserId INT) DECLARE @UserId INT --This UserId var will hold the UserId value that was derived from the UserIdList --Loop until the UserIdList var has a length of zero WHILE(LEN(@UserIdList) > 0) BEGIN print (@UserIdList) --Set the UserId to the first value in the UserIdList IF(CHARINDEX(@ListDelimiter, @UserIdList) > 0) SET @UserId = LEFT(@UserIdList, CHARINDEX(@ListDelimiter, @UserIdList) - 1) ELSE SET @UserId = @UserIdList --Remove the first value from the UserIdList SET @UserIdList = RIGHT(@UserIdList, LEN(@UserIdList) - LEN(@UserId)) --Insert the UserId into the temporary table IF(ISNUMERIC(@UserId) = 1) INSERT INTO #tmpUserList (UserId) SELECT @UserId END BEGIN -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements. SET NOCOUNT ON; -- Get all the users that have a matching UserId in the tmpUserList table SELECT DISTINCT U.UserId, U.UserName,U.Email,U.FirstName, U.LastName, U.Password, U.MobilePhone, U.IsActive, U.DateAdded FROM Users U JOIN #tmpUserList UL ON U.UserId = UL.UserId BEGIN DROP TABLE #tmpUserList END END
Thanks to Erland Sommarskog for this solution.