C# SQL Server - Passing a List to a Stored Procedure

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

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);

Pass List of strings to a stored procedure

Convert the comma seperated value to table using the XML. Use this updated procedure.

USE [App]
GO
/****** Object:  StoredProcedure [dbo].[GetWorkspaceMapDetailsForUserByGroups]    
     Script Date: 16/02/2015 10:37:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetWorkspaceMapDetailsForUserByGroups]   
    @workspaceID int,
    @viewMap nvarchar(256)
 AS 

SELECT 
  m.*
FROM 
  GeoAppMapDef m
WHERE
 m.workspaceID = @workspaceID
 and m.IsDeleted = 0
 and m.ViewMap IN 
 (
  SELECT 
     Split.a.value('.', 'VARCHAR(100)') AS CVS  
  FROM  
  (
    SELECT CAST ('<M>' + REPLACE(@viewMap, ',', '</M><M>') + '</M>' AS XML) AS CVS 
  ) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
)

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

I would go ahead and reference this page. https://docs.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.

How to make a stored procedure in which the parameters are a list of strings [ SQL Server, ASP.NET Core 3.1 ]

You can use json or xml. Try this

/* test

DECLARE @Brands nvarchar(4000) SET @Brands = '{"Brands": [{"Brand": "Samsung"}, {"Brand": "iPhone"}, {"Brand": "LG"}, {"Brand": "Xiaomi"}]}';;

DECLARE @Storages nvarchar(4000) SET @Storages = '{"Storages": [{"Storage": "16"}, {"Storage": "32"}, {"Storage": "64"}, {"Storage": "128"}]}';;

SELECT * FROM OPENJSON(@Brands, '$.Brands') WITH ( Brand varchar(20) '$.Brand' );
SELECT * FROM OPENJSON(@Storages, '$.Storages') WITH ( Storage varchar(20) '$.Storage' );

*/

    
        create procedure FilterByBrandStoragePrice
            @Brands             nvarchar(4000),
            @Storages           nvarchar(4000),
            @min                decimal,
            @max                decimal
        as
            SELECT * 
            FROM Item i
            WHERE i.Brand in (SELECT * FROM OPENJSON(@Brands, '$.Brands') WITH ( Brand varchar(20) '$.Brand' )) 
            AND i.Storage in  (SELECT * FROM OPENJSON(@Storages, '$.Storages') WITH ( Storage varchar(20) '$.Storage' ))
              AND i.PRICE between @min and @max

Need query to pass list to stored procedure in SQL Server

One way of doing this is as follows . I am assuming that you have input like this format @userId=1 , @ListOfSubjects ='asp,c#' ..
here is the sample proc . The idea is to first convert list od comma seprated string ofsubject to table and then insert it . To do that I created a sample UDF which will retun a table when comma separated string is passed to it .

        create proc SampleUser_Insert 
         @UserId int , 
        @ListOfSubjects varchar(1000)
        as
        begin

             --set @UserId=1
             --set @ListOfSubjects ='asp,c#,mvc'


             declare @sampleTable table (userId int, subjects varchar(100))

             insert into    @sampleTable (userId,subjects )

             SELECT @userId, data FROM dbo.[SplitString](@ListOfSubjects, ',')
             select * from @sampleTable

        end

    --  exec SampleUser_Insert 1,'a,b,c'

the udf is as follows

    CREATE FUNCTION [dbo].[SplitString]
    (
        @String NVARCHAR(4000),
        @Delimiter NCHAR(1)
    )
    RETURNS TABLE
    AS
    RETURN
    (
        WITH Split(stpos,endpos)
        AS(
            SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
            UNION ALL
            SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
                FROM Split
                WHERE endpos > 0
        )
        SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
            'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
        FROM Split
    )
    GO

How to pass a list of strings a sql parameter for the stored procedure to delete?

As far as I can see you are passing list of names separated by comma and you want to delete all those names. You need to use IN operator to find all holiday names that should be deleted.

Here is an example how to do it for @SelectedHolidays:

declare @SelectedHolidays nvarchar(500) = 'H1,H2,H3'
declare @SelectedHolidaysXml xml = cast(replace(N'<R><I>' + @SelectedHolidays + N'</I></R>', ',', '</I><I>') as xml)


DELETE FROM [SessionHolidayMapping]
WHERE [HolidayName] in (select x.items.value('(.)[1]', 'NVARCHAR(500)') from @SelectedHolidaysXml.nodes('/R/I') as x(items))
AND [SessionId] = @SessionId

It is ugly, but I don't know of better way to split comma separated values in sql server.

---

Related Topics