Pass Delimited List to SQL Server Stored Procedure

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
	--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)
			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)
				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

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	-- 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

		DROP TABLE #tmpUserList

Thanks to Erland Sommarskog for this solution.


One thought on “Pass Delimited List to SQL Server Stored Procedure

  1. Pingback: How to create a T-SQL Function to parse a delimited list of numbers and return a table of those numbers «

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s