posts - 81, comments - 208, 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 ]

Feedback

Gravatar

# re: Simple method to constrain stored procedures by a collection

It's best to examine every option first before making a huge decision. Impressive in here indeed. thanks for sharing.
carpet cleaning Milwaukee
5/9/2012 10:53 AM | carpet cleaning Milwaukee
Gravatar

# re: Simple method to constrain stored procedures by a collection

 El Alki de Chipre ficha a Wayne Thomas<br />
<br />
Habla Thomas<br />
<br />
El Alki de Chipre se hizo con el fichaje Haz click para comprar de Wayne Thomas de 33 años perteneciente al Atromitos griego al acabar contrato y llega a uno de los equipos El acceso a sitios relacionados revelación de la Liga Chipriota, quedó octavo, firma una temporada con otra opcional y el futbolista será presentado a lo largo de la semana próxima con el club. Ha sido una vez internacional con Inglaterra, no guarda buen recuerdo de ello camisetasdelfutbolbaratas al perder por 0-3 ante Alemania.
7/12/2012 10:25 PM | sdgsdg

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 7 and 2 and type the answer here:

Powered by:
Powered By Subtext Powered By ASP.NET