How to Pass a List as a Parameter in a Stored Procedure

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

You will have to use table valued parameters

1. Define new type as follows

   CREATE TYPE Prod_Code AS TABLE ( ProductCode varchar );
   

2. then use this type in your stored procedure

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

3. Now before calling the stored procedure fill the table

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

4. Now Call the sp like this

    EXEC dbo.proc_aggregation @PC, @Prod_Desc;
   

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

1. Build a temp table
2. Use a batch INSERT to populate the table
3. INSERT ... SELECT ... to get the items in the real table
4. 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.)

Passing a list into a stored procedure

Consider changing your type to include both pairs.

CREATE TYPE [dbo].[ProductPrices] AS TABLE(
[ProductId] [NVARCHAR](MAX) NULL,
[PriceId] [NVARCHAR](MAX) NULL
);

Then change your procedure to the following:

create procedure [dbo].[sp_create_order] @productPrices dbo.ProductPrices READONLY
as
begin
    insert into order_detail
    select p.ProductId,
        p.PriceId
    from @productPrices p
end

Then you can call your procedure as such.

declare @productPrices dbo.ProductPrices;

insert into @productPrices
values('1', '7'),
('2', '12'),
('3', '8');

exec dbo.sp_create_order @productPrices;

Passing a List of objects to SQL Server stored procedure and retrieving an output value

Here you are adding the parameter as a value for a parameter:

var contactsParam =
                    new SqlParameter("@contacts", SqlDbType.Structured)
                    {
                        TypeName = "dbo.Contact",
                        Value = GetSqlDataRecordsContactsList(contacts)
                    };

command.Parameters.Add(new SqlParameter("@contacts", contactsParam)); //Problem is here

It should just be:

 command.Parameters.Add(contactsParam);

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

---

Related Topics