Execute stored procedure with array-like string se

问题:

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.

回答1:

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

回答2:

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"]);
        }
      }
    }
  }
  

回答3:

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',','))

回答4:

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