C# SQL Server - Passing a list to a stored procedure

pass list of objects to stored procedure sql server c#
how to pass arraylist to stored procedure in sql server
how to pass array to sql server stored procedure from c#
pass list to stored procedure c# entity framework
pass list of int to stored procedure sql server
how to pass list in sql query c#
how to pass array to sql server stored procedure from java
pass key-value pair to stored procedure sql server

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

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

C# SQL Server - Passing a list to a stored procedure, to pass data-table to a stored procedure in SQL server from C# of SQL Server, I've used to the XML method to pass array or list to stored� I am working on asp.net application. I am using ado.net to access the SQL Server database and perform CRUD. I want to insert multiple records in one go. For this purpose, I have added a custom Type in database and a stored procedure like this:

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.

How To Pass Array Or List To Stored Procedure, I recently had a requirement to update multiple tables with the same value. We have a table that stores information about documents (Excel� C# SQL Server - Passing a list to a stored procedure (8 answers) Closed 5 years ago . Using, SQL Server 2012, I would like to create a stored procedure, that passes in a list of string and checks it for each entry.Iv added the list to one comma separated string 'UserGroupsAllowedToViewMap'.

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.

Passing a list of items to a SQL stored procedure, That's actually a lot harder than it sounds, because SQL deosn't have a concept of "lists" or even "arrays". Depending on exactly what you want� While using older versions of SQL Server, I’ve used to the XML method to pass array or list to stored procedure. In the latest versions of SQL Server, we can use the User Defined Data Type (UDT) with a base type of table to send array or list through a parameter.

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

how to pass list to stored procedure, I will consider few solutions: creation of sql-query at server code, put set At last C# code, which will put filter to stored procedure and get list of� But as per his requirement, he wants to pass only list of int value. For that i dont think we need to create table valued param. He is passing just list of values, not a table actually. Even in future if he wants to change the data type then You cannot use ALTER TABLE statements to modify the design of table-valued parameters. - Sugumar

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.

TSQL: Passing Array/List/Set to Stored Procedure (Microsoft SQL , How to pass an array into a SQL Server stored procedure using c# Pass array into stored Duration: 11:57 Posted: Jun 3, 2019 TSQL: Passing array/list/set to stored procedure (Microsoft SQL Server) Passing array/list/set to stored procedure is a fairly common task when you are working with databases. You can meet this when you want to filter some collection.

Pass an array into a SQL Server stored procedure - C#, For more information, please refer to thread C# SQL Server - Passing a list to a stored procedure. If you have any more questions related this,� 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.

pass List item as input parameter to Sqlserver - MSDN, This is essentially SQL Server's solution to your problem--passing in a list of values to a stored procedure. make one stored procedure call with all your data passed in as 1 parameter; table input is structured and C# and TSQL code below: There are several other methods for passing arrays to SQL stored procedures, although they deal with increasing complexity. One such method includes parsing a comma-separated list of values into a temporary table which is then joined with a main table.

Passing array parameters to a stored procedure, To clarify and answer Nick's question, I'd like to pass in a List of values from C# to SQL Server and be able to do a select similar to the SQL I posted above. This� Passing table-valued parameters to a stored procedure is a three-step process: Create a user-defined table type that corresponds to the table that you want to populate. Pass the user-defined table to the stored procedure as a parameter

Comments
  • Possibly, duplicate of stackoverflow.com/questions/209686/…
  • What version of SQL Server?? 2005?? 2008 ?? 2008 R2 ?? SQL Server 2008 and newer has the concept of "table-valued parameters" (see Redth's response for details)
  • Unrelated tip: the SqlDataReader is also IDisposable so should be in a using block.
  • Does it have to define a datatable to set the value of the parameter? Any other light approach?
  • We tried it but we found its drawback being not supported by Enitiy framework
  • 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....
  • @Fazi in this case, don't use Linq2SQL. It's preferable to string concatenation and parsing in T-SQL
  • Yeah, so how do you actually execute this from SSMS?
  • let me throw in a link for a dbo.SplitCommaString implementation for completeness: goo.gl/P9ROs
  • And what happens when there is a comma in one of your data fields?
  • Delimit it with pipes instead.
  • @AlexInParis What if there is a pipe in one of your data fields?
  • 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 §.
  • that's the way to go. I actually created a table on the db side and loaded with bcp write to server.
  • Thanks. Changed to a stack overflow question instead.