posts - 81, comments - 262, trackbacks - 0

Simple method to constrain stored procedures by a collection

Passing a collection of ID's to filter a SQL query within a stored procedure is not natively supported. Consider this article which discusses methods for passing arrays into a stored procedure.

Constraining a stored procedure's query has a simple work around, however. Passing the ID collection as a comma deliminated string allows the query to use LIKE to constrain the results. Note that the preceding and trailing commas are necessary. An example demonstrates how to do this easily.


DECLARE @Ids varchar(max);

set @Ids = ',1,2,3,4,5,';

Select * from [TableName]

    WHERE @Ids LIKE ('%,' + cast(TableID as varchar(50)) + ',%')

Print | posted on Saturday, April 7, 2012 4:12 PM | Filed Under [ Web Programming ]

Powered by:
Powered By Subtext Powered By ASP.NET