Passing Multiple Values in One Parameter to Query a Table


This is a frequently asked question from users at ASP.NET forum. The solution to this problem to SPLIT the multiple values into a single column table and use this table to JOIN the target table we are querying. Here is a simple stored procedure to do this:

Create Procedure [dbo].[myPROCEDURE]

( @ID varchar(50)=NULL)

AS

Begin

WITH cte

AS (SELECT Cast(‘<M>’ + Replace( @id , ‘,’ ,'</M><M>’) + ‘</M>’ AS XML) AS id)

,mycte2 as

(SELECT S.a.value(‘.’, ‘VARCHAR(100)’) AS id FROM cte CROSS APPLY id.nodes(‘/M’) S(a))

Select * from mytable m INNER JOIN mycte2 t on m.id=t.id

End

 

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s