SQL Reloaded: Lists as Parameters in Stored Procedures

In the previous post I shown how a delimited list can be transformed in a table with the help of table-valued functions. In this posting I'll show how to use lists in stored procedures without the need to create the query as a string. Let's create a table and populate it from a list:

-- creating the table
CREATE TABLE dbo.Testing( 
 value varchar(50))  

--populating it with records
INSERT dbo.Testing 
SELECT value
FROM dbo.SplitList('1,5,7,56,67,34,33,54,76', ',') 

-- reviewing the output
SELECT * 
FROM Testing 

 Normally in a stored procedure, if you want to limit your criteria on a dynamic range or values provided as parameters then you would have to concatenate your script dynamically like below: 

-- creating the stored procedure
CREATE PROCEDURE dbo.TestingListsAsParameters( 
 @listOfValues varchar(500)) 
AS 
DECLARE @SQL varchar(1000) 
SET @SQL = 'SELECT * FROM dbo.Testing WHERE Value IN (' + @listOfValues + ')' 
EXEC (@SQL) 

-- testing the stored procedure
EXEC TestingListsAsParameters '5,7,56, 67' 

Same result can be obtained by using the following method: 

-- creating the stored procedure (join-based logic)
CREATE PROCEDURE dbo.TestingListsAsParameters1( 
@listOfValues varchar(500)) 
AS 
SELECT A.* 
FROM dbo.Testing A 
     JOIN dbo.SplitList(@listOfValues, ',') B 
       ON A.Value = B.Value

-- testing the stored procedure 
EXEC TestingListsAsParameters1 '5,7,56,67' 

Sometimes we want to return all the records if the list provided as parameter contains no values. The above stored procedure can be written as follows: 

-- creating the stored procedure 
CREATE PROCEDURE dbo.TestingListsAsParameters2( 
@listOfValues varchar(500)='') 
AS 
SELECT A.* 
FROM Testing A 
WHERE (Value IN (SELECT value FROM dbo.SplitList(@listOfValues, ',')) 
     AND Len(@listOfValues)>0) 
        OR Len(@listOfValues)=0 

--testing the stored procedure 
EXEC TestingListsAsParameters2 '5,7,56,67' --returns only the matches from the list 
EXEC TestingListsAsParameters2 --returns all the values 

The method can be extended to support multiple lists as parameters.