C SQL Server - Passing a list to a stored procedure

Question

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 lt string gt   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  lt LIST OF STRINGS GOES HERE gt    However  the list of strings is dynamic and a few hundred long   Is there a way to pass a list of strings List lt string gt  to the stored proc    Or is there a better way to do this   Many thanks  Brett

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  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

User · Answer

CREATE TYPE  dbo   StringList1  AS TABLE   Item   NVARCHAR  MAX  NULL   counts  nvarchar  20  NULL     create a TYPE as table and name it as StringList1   create PROCEDURE  dbo   sp UseStringList1   list StringList1 READONLY AS BEGIN     -- Just return the items we passed in     SELECT l item l counts FROM  list l      SELECT l item l counts into tempTable FROM  list l   End   The create a procedure as above and name it as  UserStringList1   s  String strConnection   ConfigurationManager ConnectionStrings  DefaultConnection   ConnectionString ToString                SqlConnection con   new SqlConnection strConnection               con Open                var table   new DataTable                 table Columns Add  Item   typeof string                table Columns Add  count   typeof string                 for  int i   0  i  lt  10  i                                  table Rows Add i ToString     i i  ToString                                    SqlCommand cmd   new SqlCommand  exec sp UseStringList1  list   con                         var pList   new SqlParameter   list   SqlDbType Structured                       pList TypeName    dbo StringList1                       pList Value   table                       cmd Parameters Add pList                       string result   string Empty                      string counts   string Empty                      var dr   cmd ExecuteReader                         while  dr Read                                                  result    dr  Item   ToString                            counts    dr  counts   ToString                            in the c  Try this

User · Answer

No  arrays lists can t be passed to SQL Server directly   The following options are available    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   Pass xml and have a function in SQL Server parse the XML for each value in the list Use the new defined User Defined table type  SQL 2008  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

User · Answer

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

[c#] C# SQL Server - Passing a list to a stored procedure

Examples related to c#

Examples related to sql

Examples related to stored-procedures