13 September 2007

Lists as Parameters in Stored Procedures In the previous post I've shown how we can transform a delimited list in a table with the help of table-valued functions, in this posting I'll show how we can use lists in stored procedures without the need to create the query as a string. Let's create a table which will be used for testing purposes: CREATE TABLE Testing( value varchar(50)) and populate it from a list: INSERT Testing SELECT value FROM dbo.SplitList('1,5,7,56,67,34,33,54,76', ',') Let's see the values: 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: CREATE PROCEDURE TestingListsAsParameters( @listOfValues varchar(500)) AS DECLARE @SQL varchar(1000) SET @SQL = 'SELECT * FROM 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: CREATE PROCEDURE TestingListsAsParameters1( @listOfValues varchar(500)) AS SELECT A.* FROM Testing A JOIN dbo.SplitList(@listOfValues, ',') B ON A.Value = B.Value EXEC TestingListsAsParameters1 '5,7,56,67' nbsp; Sometimes we want if the list contains no values to return all the records from the targeted table, we can rewrite the above procedure as follows: CREATE PROCEDURE 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 EXEC TestingListsAsParameters2 '5,7,56,67' --returns only the matches from the list EXEC TestingListsAsParameters2 --returns all the values nbsp; The method can be thus extended to support multiple lists as parameters.

No comments: