Execute stored procedure with array-like string separated by comma [duplicate]

Question

Possible Duplicate:
Help with a sql search query using a comma delimitted parameter

I want to write a stored procedure that performs a select on a table and need one input variable of type varchar(max).

I'd like to send a bunch of values separated by , as the input parameter, e.g.

'Jack','Jane','Joe'

and then get the rows that contain one of these names.

In SQL the code would be

Select * from Personnel where Name in ('Jack','Joe','Jane');

Now I want to have a variable in my C# app, say strNames and fill it like

string strNames = "'Jack','Joe','Jane'";

and send this variable to the SP and execute it. Something like

Select * from Personnel where Name in (''Jack','Joe','Jane'') -- this is wrong

But how can I tell SQL Server to run such command?

I need to make this happen and I know it's possible, please give me the clue.

Is is possible to send for example this string Select * from Table1 to a stored procedure and the sp executes it as a command? I somehow want sql to execute a string. — Mahdi Tahsildari
– Mahdi Tahsildari, Commented Jan 10, 2013 at 12:04
Vulnerability is one thing. You also lose all comfort provided by the .NET framework, for example the fact that parameters are always passed to the query in the right format, etc. (think of i18 issues for date or floating point values). Also, if you want to send the statement to a stored procedure, you could as well right away execute it in your application - what's the difference? — Thorsten Dittmar
– Thorsten Dittmar, Commented Jan 10, 2013 at 12:10
Mahdi. You can do that with the sp_executesql command: msdn.microsoft.com/en-us/library/ms188001.aspx It does have it's place. But you need to be very cautious using it. My main concern would be around making sure you are not "building" your sql command using user input, and hence sql-injection. — Zippit
– Zippit, Commented Jan 10, 2013 at 13:16

Thorsten Dittmar · Accepted Answer · 2014-05-22 14:13:18Z

10

First of all, the single names don't need to be quoted when you pass them to the stored procedure.

using (SqlCommand cmd = new SqlCommand("MyStoredProc", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@longFilter", "Jack,Jill,Joe");

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        ...
    }
}

Then, in the stored procedure, you can use simple text functions and a temporary table as follows to split up the string at the commas and an an entry to the temporary table for each part of the string:

DECLARE @temp AS TABLE (Name NVARCHAR(255))

IF ISNULL(@longFilter, '') <> ''
BEGIN
    DECLARE @s NVARCHAR(max)
    WHILE LEN(@longFilter) > 0
    BEGIN
        IF CHARINDEX(',', @longFilter) > 0
        BEGIN
            SET @s = LTRIM(RTRIM(SUBSTRING(@longFilter, 1, CHARINDEX(',', @longFilter) - 1)))
            SET @longFilter = SUBSTRING(@longFilter, CHARINDEX(',', @longFilter) + 1, LEN(@longFilter))
        END ELSE
        BEGIN
            SET @s = LTRIM(RTRIM(@longFilter))
            SET @longFilter= ''
        END

        -- This was missing until 20140522
        INSERT INTO @temp (Name) VALUES (@s)
    END
END

Later use the following SELECT to get a list of all people the name of which is in @temp or all of them if @temp doesn't contain any rows (unfiltered result):

SELECT * FROM Personnel WHERE Name IN (SELECT Name FROM @temp) OR (SELECT COUNT(*) FROM @temp) = 0

edited May 22, 2014 at 14:13

answered Jan 10, 2013 at 12:02

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bynary Over a year ago

How does this work? It doesn't look like the longFilter values are ever getting stored in the temp table. Is some of the SQL code missing? Am I missing something obvious?

Thorsten Dittmar Over a year ago

Actually, you're right (funny nobody noticed it before) - the actual INSERT into the temp table was missing. I added it with a comment. Thanks for pointing that out!

Community · Accepted Answer · 2017-05-23 12:16:57Z

You could use Table Valued Parameters.

Basically, you could insert a list of values as a parameter in the procedure, and use them as a table, something along the lines of

Select * from Personnel 
  where Name in (select name from @NamesTable).

Now, the specifics

To use table valued parameters, the type of the parameter must be predefined in sql server, using
```
create type NamesTable as table (Name varchar(50))
```

You can then use the defined type as a parameter in the procedure

create procedure getPersonnelList
  @NamesTable NamesTable readonly
as
begin
  select * from personnel
    where Name in (select Name from @NamesTable)
end

You can see that in action, in this SQL Fiddle

On the C# side of things you need to create the parameter. If you have the names in a collection, and build the string, you can just use that to generate the parameter, and if they are a comma-separated string, a quick string.Split could take care of that. Since I do not know your specifics, I'll assume you have a List<string> called names. You'll need to convert that to a table valued parameter to be sent to the procedure, using something like:
```
DataTable tvparameter = new DataTable();
tvparameter.Columns.Add("Name", typeof(string));
foreach (string name in names)
{
  tvparameter.Rows.Add(name);
}
```
You can find more info on how to generate a TVP in C# code in the SO Question..

Now you just need to send that parameter to the procedure, and that's that. Here is a complete console program that executes the procedure and outputs the results.

List<string> names = new List<string> { "Joe", "Jane", "Jack" };

using (SqlConnection cnn = new SqlConnection("..."))
{
  cnn.Open();
  using (SqlCommand cmd = new SqlCommand("getPersonnelList", cnn))
  {
    cmd.CommandType = CommandType.StoredProcedure;

    DataTable tvparameter = new DataTable();
    tvparameter.Columns.Add("Name", typeof(string));
    foreach (string name in names)
    {
      tvparameter.Rows.Add(name);
    }

    cmd.Parameters.AddWithValue("@NamesTable", tvparameter);

    using (SqlDataReader dr = cmd.ExecuteReader())
    {
      while (dr.Read())
      {
        Console.WriteLine("{0} - {1}", dr["ID"], dr["Name"]);
      }
    }
  }
}

TechDo · Accepted Answer · 2013-01-10 11:57:35Z

2

I guess you need Split Function in Sql Server to break Comma-Separated Strings into Table. Please refer these links.

Split Function in Sql Server to break Comma-Separated Strings into Table

SQL User Defined Function to Parse a Delimited String

You can select the data from table using

Select * from 
Personnel where 
  Name in (select items from dbo.Split ('Jack,Joe,Jane',','))

answered Jan 10, 2013 at 11:57

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

1 Comment

MarkD Over a year ago

The "best" function I know for this purpose can be found at sqlservercentral.com/articles/Tally+Table/72993

Rhumborl · Accepted Answer · 2013-01-10 12:04:16Z

2

You could simply check if Name is contained in the string. Note the commas at the start of the end to ensure you match the full name

string strNames = ",Jack,Joe,Jane,";

The the SQL becomes

select * from Personnel where PATINDEX('%,' + Name + ',%', @strNames) > 0

See http://www.sqlfiddle.com/#!3/8ee5a/1

answered Jan 10, 2013 at 12:04

Rhumborl

16.7k4 gold badges41 silver badges47 bronze badges

Collectives™ on Stack Overflow

Execute stored procedure with array-like string separated by comma [duplicate]

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Linked

Related