- IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.
Thursday, September 13, 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.