Passing array to a SQL Server Stored Procedure

Question

How can I pass an array variable to a SQL Server stored procedure using C# and insert array values into a whole row?

Thanks in advance.

SQL Server table:

ID    | Product | Description
-------------------------------
8A3H  | Soda    | 600ml bottle

C# array:

string[] info = new string[] {"7J9P", "Soda", "2000ml bottle"};

SQL Server stored procedure:

ALTER PROC INSERT
    (@INFO_ARRAY ARRAY)
AS
BEGIN
    INSERT INTO Products VALUES (@INFO_ARRAY)
END

Is there any reason why you can't just use multiple parameters or are you just looking for a shortcut? — D Stanley, Jul 08 '14 at 16:12
This might help - http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure?rq=1 — Daniel Kelley, Jul 08 '14 at 16:13
You can use a [TVP](http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx). — Kaf, Jul 08 '14 at 16:17
Sorry, that's not a simple option in T-SQL. You could probably get it to work with dynamic sql or table-valued parameters but it would be cleaner and safer to use multiple parameters. — D Stanley, Jul 08 '14 at 16:19
http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/ Tells it all And is very efficient — paparazzo, Jul 08 '14 at 16:48

score 22 · Accepted Answer · edited Jan 29 '16 at 18:37

22

In SQL Server 2008 and later

Create a type in SQL Server like so:

CREATE TYPE dbo.ProductArray 
AS TABLE
(
  ID INT,
  Product NVARCHAR(50),
  Description NVARCHAR(255)
);

Alter your procedure in SQL Server:

ALTER PROC INSERT_SP
@INFO_ARRAY AS dbo.ProductArray READONLY
AS
BEGIN
    INSERT INTO Products SELECT * FROM @INFO_ARRAY
END

Then you'll need to create a DataTable object with values to pass in C#:

DataTable dt = new DataTable();
//Add Columns
dt.Columns.Add("ID");
dt.Columns.Add("Product");
dt.Columns.Add("Description");
//Add rows
dt.Rows.Add("7J9P", "Soda", "2000ml bottle");

using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.INSERT_SP", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter dtparam = cmd.Parameters.AddWithValue("@INFO_ARRAY", dt);
    dtparam.SqlDbType = SqlDbType.Structured;
}

edited Jan 29 '16 at 18:37

Mykola

3,343
6
23
39

answered Jul 08 '14 at 16:22

kerbasaurus

493
3
10

This looks almost identical to the answer in the link I posted above. Did you write this yourself or have you changed that answer? – Daniel Kelley Jul 08 '14 at 16:36
I wrote this based on code I wrote a few weeks back, based on an article I found here: [link](http://www.codeproject.com/Questions/631284/How-to-pass-an-array-to-SQL-SERVER-stored-pro) – kerbasaurus Jul 08 '14 at 16:42
I have an error in the Stored Procedure: "Must declare a scalar variable "@INFO_ARRAY"" @kerbasaurus – user3658439 Jul 08 '14 at 16:52
1

"INFO_ARRAY" is a table, not a scalar, so in your stored proc, INSERT INTO Products VALUES (@INFO_ARRAY) needs to be changed to INSERT INTO Products SELECT * FROM @INFO_ARRAY – Kevin Suchlicki Jul 08 '14 at 17:25
Fix the 2nd to last line to read, `dtparam.SqlDbType = SqlDbType.Structured;` – Jakub Keller Jun 30 '15 at 13:25
What do you do when you have to use `DbType` instead of `SqlDbType`? – Panzercrisis Nov 07 '16 at 21:50

score 2 · Answer 2 · answered Jul 17 '14 at 16:37

here is a way simpler example:

I've been searching through all the examples and answers of how to pass any array to sql server,till i found this linK, below is how I applied it to my project:

--The following code is going to get an Array as Parameter and insert the values of that --array into another table

Create Procedure Proc1 



@INFO_ARRAY ARRAY nvarchar(max)  //this is the array your going to pass from C# code

AS

    declare @xml xml

    set @xml = N'<root><r>' + replace(@INFO_ARRAY,',','</r><r>') + '</r></root>'

    Insert into Products 
    select 
     t.value('.','varchar(max)')


    from @xml.nodes('//root/r') as a(t)
END

Hope you enjoy it

Passing array to a SQL Server Stored Procedure

2 Answers2

Linked