Passing List<> to SQL Stored Procedure

Pass list of values to be inserted as parameter to stored procedure

The best efficient generalization, especially for long lists, I have found is to

Build a temp table
Use a batch INSERT to populate the table
INSERT ... SELECT ... to get the items in the real table
Do a complex update with a join to pull back all the ids.

(No loops involved.)

Specifics: http://mysql.rjweb.org/doc.php/staging_table#normalization

(That is focused around the very likely situation where the names are already in the table. However, it should work fine for your case.)

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

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

How to pass an array into a SQL Server stored procedure

SQL Server 2016 (or newer)

You can pass in a delimited list or JSON and use STRING_SPLIT() or OPENJSON().

STRING_SPLIT():

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List varchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT value FROM STRING_SPLIT(@List, ',');
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

OPENJSON():

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List varchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT value FROM OPENJSON(CONCAT('["',
    REPLACE(STRING_ESCAPE(@List, 'JSON'), 
    ',', '","'), '"]')) AS j;
END
GO
EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

I wrote more about this here:

Handling an unknown number of parameters in SQL Server
Ordered String Splitting in SQL Server with OPENJSON

SQL Server 2008 (or newer)

First, in your database, create the following two objects:

CREATE TYPE dbo.IDList
AS TABLE
(
  ID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.IDList READONLY
AS
BEGIN
  SET NOCOUNT ON;
  
  SELECT ID FROM @List; 
END
GO

Now in your C# code:

// Obtain your list of ids to send, this is just an example call to a helper utility function
int[] employeeIds = GetEmployeeIds();

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));

// populate DataTable from your List here
foreach(var id in employeeIds)
    tvp.Rows.Add(id);

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    // these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
    tvparam.SqlDbType = SqlDbType.Structured;
    tvparam.TypeName = "dbo.IDList";
    // execute query, consume results, etc. here
}

SQL Server 2005

If you are using SQL Server 2005, I would still recommend a split function over XML. First, create a function:

CREATE FUNCTION dbo.SplitInts
(
   @List      VARCHAR(MAX),
   @Delimiter VARCHAR(255)
)
RETURNS TABLE
AS
  RETURN ( SELECT Item = CONVERT(INT, Item) FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );
GO

Now your stored procedure can just be:

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;
  
  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); 
END
GO

And in your C# code you just have to pass the list as '1,2,3,12'...

I find the method of passing through table valued parameters simplifies the maintainability of a solution that uses it and often has increased performance compared to other implementations including XML and string splitting.

The inputs are clearly defined (no one has to guess if the delimiter is a comma or a semi-colon) and we do not have dependencies on other processing functions that are not obvious without inspecting the code for the stored procedure.

Compared to solutions involving user defined XML schema instead of UDTs, this involves a similar number of steps but in my experience is far simpler code to manage, maintain and read.

In many solutions you may only need one or a few of these UDTs (User defined Types) that you re-use for many stored procedures. As with this example, the common requirement is to pass through a list of ID pointers, the function name describes what context those Ids should represent, the type name should be generic.

How to pass a list of strings as a parameter in a stored procedure in SQL?

You will have to use table valued parameters

Define new type as follows

CREATE TYPE Prod_Code AS TABLE ( ProductCode varchar );

then use this type in your stored procedure

 create procedure [dbo].[proc_aggregation]
 @Prod_Code Prod_Code READONLY,
 @Prod_Desc varchar (30)
 as
 ......

Now before calling the stored procedure fill the table

 declare @PC Prod_Code;
 insert @PC VALUES ('12012'), ('12011'), ('12014')

Now Call the sp like this

 EXEC dbo.proc_aggregation @PC, @Prod_Desc;

How can I send a C# model list to a stored procedure in SQL?

I would go ahead and reference this page. https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017. You can create a User Defined Table Type and set up a stored procedure to take you user defined table type as input.

If you're using SqlCommand you then just add your list of objects under comm.Parameters before you call ExecuteNonQuery. Note that your object's names and types must match your user defined table type.

I see that other have recommended doing the calls individually. This is often fine so long as you can manage your connections. If the operation is inserting a lot of data in a loop and you call a procedure to add records 1 at a time you can get connection issues.

Calling Stored Procedure With List Parameter

The create statement you posted didn't work in SQL Server 2017, so I'll show you a simpler example.

DDLs:

create table Users (
  id int,
  name varchar(20)
);

create type UserTableType as table (
  id int,
  name varchar(20)
);

create procedure uspAddUsers
  @UserTable UserTableType READONLY
as
begin
    insert into Users (id, name)
      select * from @UserTable
end;

POJO:

public class User {
  private Integer id;
  private String name;
  // getter/setter
}

Mapper method:

@Options(statementType = StatementType.CALLABLE)
@Insert("{call uspAddUsers(#{users,typeHandler=pkg.UserListTypeHandler})}")
void insertUsers(@Param("users") List<User> users);

Note the typeHandler option.

As David Browne pointed out, the driver requires SQLServerDataType as the input, so you may need a type handler that converts the list into a SQLServerDataType.

The below is a simple type handler implementation.

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import com.microsoft.sqlserver.jdbc.SQLServerDataTable;
import com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement;

public class UserListTypeHandler extends BaseTypeHandler<List<User>>{
  @Override
  public void setNonNullParameter(PreparedStatement ps,
      int i, List<User> parameter, JdbcType jdbcType)
      throws SQLException {
    SQLServerDataTable dataTable = new SQLServerDataTable();
    dataTable.addColumnMetadata("id", java.sql.Types.INTEGER);
    dataTable.addColumnMetadata("name", java.sql.Types.VARCHAR);
    for (User user : parameter) {
      dataTable.addRow(user.getId(), user.getName());
    }
    ps.unwrap(SQLServerPreparedStatement.class)
      .setStructured(i, "UserTableType", dataTable);
  }
  // getNullableResult() won't be used
}

An executable demo tested with...

Microsoft SQL Server 2017 (RTM-CU15) (KB4498951) - 14.0.3162.1 (X64)
mssql-jdbc 7.3.1.jre8-preview

Pass List as Sql Table Type Parameter

Finally, i got my answer byself. But during finding, i got that there is no way exist to convert List<> to IEnumerable<> directly.

But this article is very useful to transact data through object or List<Obj>

http://www.c-sharpcorner.com/UploadFile/pchandraker/passing-table-valued-parameter-to-stored-procedu-part-2/

very useful. :)