By Brett


2011-08-17 17:40:34 8 Comments

I am calling a SQL Server stored procedure from my C# code:

using (SqlConnection conn = new SqlConnection(connstring))
{
   conn.Open();
   using (SqlCommand cmd = new SqlCommand("InsertQuerySPROC", conn))
   {
      cmd.CommandType = CommandType.StoredProcedure;

      var STableParameter = cmd.Parameters.AddWithValue("@QueryTable", QueryTable);
      var NDistanceParameter = cmd.Parameters.AddWithValue("@NDistanceThreshold", NDistanceThreshold);
      var RDistanceParameter = cmd.Parameters.AddWithValue(@"RDistanceThreshold", RDistanceThreshold);

      STableParameter .SqlDbType = SqlDbType.Structured;
      NDistanceParameter.SqlDbType = SqlDbType.Int;
      RDistanceParameter.SqlDbType = SqlDbType.Int;

      // Execute the query
      SqlDataReader QueryReader = cmd.ExecuteReader();

My stored proc is fairly standard but does a join with QueryTable (hence the need for for using a stored proc).

Now: I want to add a list of strings, List<string>, to the parameter set. For example, my stored proc query goes like this:

SELECT feature 
FROM table1 t1 
INNER JOIN @QueryTable t2 ON t1.fid = t2.fid 
WHERE title IN <LIST_OF_STRINGS_GOES_HERE>

However, the list of strings is dynamic and a few hundred long.

Is there a way to pass a list of strings List<string> to the stored proc??? Or is there a better way to do this?

Many thanks, Brett

7 comments

@Redth 2011-08-17 17:52:25

If you're using SQL Server 2008, there's a new featured called a User Defined Table Type. Here is an example of how to use it:

Create your User Defined Table Type:

CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

Next you need to use it properly in your stored procedure:

CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Just return the items we passed in
    SELECT l.Item FROM @list l;
END

Finally here's some sql to use it in c#:

using (var con = new SqlConnection(connstring))
{
    con.Open();

    using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))
    {
        using (var table = new DataTable()) {
          table.Columns.Add("Item", typeof(string));

          for (int i = 0; i < 10; i++)
            table.Rows.Add("Item " + i.ToString());

          var pList = new SqlParameter("@list", SqlDbType.Structured);
          pList.TypeName = "dbo.StringList";
          pList.Value = table;

          cmd.Parameters.Add(pList);

          using (var dr = cmd.ExecuteReader())
          {
            while (dr.Read())
                Console.WriteLine(dr["Item"].ToString());
          }
         }
    }
}

To execute this from SSMS

DECLARE @list AS StringList

INSERT INTO @list VALUES ('Apple')
INSERT INTO @list VALUES ('Banana')
INSERT INTO @list VALUES ('Orange')

-- Alternatively, you can populate @list with an INSERT-SELECT
INSERT INTO @list
   SELECT Name FROM Fruits

EXEC sp_UseStringList @list

@ca9163d9 2012-12-13 06:56:23

Does it have to define a datatable to set the value of the parameter? Any other light approach?

@Bishoy Hanna 2014-08-12 06:12:23

We tried it but we found its drawback being not supported by Enitiy framework

@JaKXz 2014-12-10 21:08:00

I'm trying to use this with the IN clause like so: SELECT [columnA] FROM [MyTable] WHERE [Id] IN (@List) but when trying to update my Stored Procedure to do this, SQL Server 2012 tells me that I have to Declare the scalar variable @List. I'm using it in the parameters section like you are so when I try to put DECLARE in front of it I get lots of syntax errors... any ideas @Redth ?

@Fazi 2015-02-10 15:57:33

BE CAREFUL WITH THIS SOLUTION IF YOU ARE USIN LINQ2SQL, AS IT DOES NOT SUPPORT USER DEFINED TABLE TYPES AS PARAMETERS!!! A workaround can be found in Jon Raynor's answer, using comma separated lists and a parser function, however this also has drawbacks....

@Panagiotis Kanavos 2015-02-16 12:14:29

@Fazi in this case, don't use Linq2SQL. It's preferable to string concatenation and parsing in T-SQL

@Sinaesthetic 2017-04-04 23:35:39

Yeah, so how do you actually execute this from SSMS?

@RollRoll 2017-12-15 15:09:31

@ca9163d9 Does it accept list<> of strings instead of DataTable?

@Walter Stabosz 2018-11-29 04:49:36

@Sinaesthetic I edited the answer to add a example of how to execute from SSMS

@Larry Silverman 2012-03-08 23:25:09

If you prefer splitting a CSV list in SQL, there's a different way to do it using Common Table Expressions (CTEs). See Efficient way to string split using CTE.

@InfinitiesLoop 2014-10-01 03:05:30

Link appears dead

@Larry Silverman 2014-10-01 17:06:31

Thanks. Changed to a stack overflow question instead.

@Jon Raynor 2011-08-17 18:09:19

No, arrays/lists can't be passed to SQL Server directly.

The following options are available:

  1. Passing a comma-delimited list and then having a function in SQL split the list. The comma delimited list will most likely be passed as an Nvarchar()
  2. Pass xml and have a function in SQL Server parse the XML for each value in the list
  3. Use the new defined User Defined table type (SQL 2008)
  4. Dynamically build the SQL and pass in the raw list as "1,2,3,4" and build the SQL statement. This is prone to SQL injection attacks, but it will work.

@hungryMind 2011-08-17 17:49:34

Make a datatable with one column instead of List and add strings to the table. You can pass this datatable as structured type and perform another join with title field of your table.

@dier 2018-08-06 12:30:57

that's the way to go. I actually created a table on the db side and loaded with bcp write to server.

@sll 2011-08-17 17:45:33

Yep, make Stored proc parameter as VARCHAR(...) And then pass comma separated values to a stored procedure.

If you are using Sql Server 2008 you can leverage TVP (Table Value Parameters): SQL 2008 TVP and LINQ if structure of QueryTable more complex than array of strings otherwise it would be an overkill because requires table type to be created within SQl Server

@Tejs 2011-08-17 17:45:31

The typical pattern in this situation is to pass the elements in a comma delimited list, and then in SQL split that out into a table you can use. Most people usually create a specified function for doing this like:

 INSERT INTO <SomeTempTable>
 SELECT item FROM dbo.SplitCommaString(@myParameter)

And then you can use it in other queries.

@Veli Gebrev 2011-09-29 13:49:01

let me throw in a link for a dbo.SplitCommaString implementation for completeness: goo.gl/P9ROs

@Kevin Panko 2014-06-27 20:30:14

And what happens when there is a comma in one of your data fields?

@Alex In Paris 2016-11-18 09:14:49

Delimit it with pipes instead.

@RayLoveless 2017-10-23 20:29:25

@AlexInParis What if there is a pipe in one of your data fields?

@Alex In Paris 2017-10-24 12:57:33

Then use something that isn't in your data fields. Clean your data, if necessary but not much data I have ever seen has ever used pipes. If they're absolutely necessary find some other character like ¤ or §.

@Andrey Agibalov 2011-08-17 17:44:05

The only way I'm aware of is building CSV list and then passing it as string. Then, on SP side, just split it and do whatever you need.

Related Questions

Sponsored Content

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

45 Answered Questions

26 Answered Questions

42 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

37 Answered Questions

9 Answered Questions

[SOLVED] How to pass an array into a SQL Server stored procedure

1 Answered Questions

[SOLVED] C# SQL Columns into ComboBox

  • 2009-12-07 20:41:16
  • user222427
  • 4285 View
  • 0 Score
  • 1 Answer
  • Tags:   c#

Sponsored Content