Stored procedure - Multiple string parameters with IN clause sql

Question

i want to filter the rows using Stored procedure. below is my query

   Create PROCEDURE [dbo].[Test]      
   @SearchTerm VARCHAR(100)        
   AS      
   BEGIN     

        Select * from master where name in (@SearchTerm)

   END

In code behind,

    cmd.Parameters.AddWithValue("@SearchTerm", "peter")

when i run with above parameter, it's work fine.

but when i pass like this cmd.Parameters.AddWithValue("@SearchTerm", "'peter','rahul'")

this time no rows fetching.

i tried manually then also it's not working.

    exec Test ''peter','rahul''

Please help me, how to pass muliple string Using IN clause?

Passing array of values into the SP is not that easy. See this [discussion](http://stackoverflow.com/questions/1069311/passing-an-array-of-parameters-to-a-stored-procedure) with a couple of options. — Andrei, Apr 10 '14 at 10:44
yea, this answer http://stackoverflow.com/a/1069388/223752 solves your issue — Nitin Sawant, Apr 10 '14 at 10:45

score 0 · Answer 1 · answered Apr 10 '14 at 10:47

One method is

Create PROCEDURE [dbo].[Test]      
   @SearchTerm VARCHAR(100)        
   AS      
    BEGIN     
      Select * from master where ','+@SearchTerm+',' like '%,'+name+',%'

You can find more methods at http://www.sommarskog.se/arrays-in-sql-2008.html

Stored procedure - Multiple string parameters with IN clause sql

1 Answers1