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.
Constructing SQL Server stored procedure for array Input
The issue comes down to much is how long is the list of ID's you going to pass to t-sql is the issue?
You could take the passed list (assume it is a string), say like this from Access at a PT query
exec GetHotels '1,2,3,4,5,6,7,10,20,30'
So, the above is the PT query you can/could send to sql server from Access.
So, in above, we want to return records based on above?
The T-SQL would thus become:
CREATE PROCEDURE GetHotels
@IdList nvarchar(max)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MySQL nvarchar(max)
set @MySQL = 'select * from tblHotels where ID in (' + @IdList + ')'
EXECUTE sp_executesql @mysql
END
GO
Now, in Access, say you have that array of "ID" ? You code will look like this:
Sub MyListQuery(MyList() As String)
' above assumes a array of id
' take array - convert to a string list
Dim strMyList As String
strMyList = "'" & Join(MyList, ",") & "'"
Dim rst As DAO.Recordset
With CurrentDb.QueryDefs("qryPassR")
.SQL = "GetHotels " & strMyList
Set rst = .OpenRecordset
End With
rst.MoveLast
Debug.Print rst.RecordCount
End Sub
Unfortunately, creating t-sql on the fly is a "less" then ideal approach. In most cases, because the table is not known at runtime, you have to specific add EXEC permissions to the user.
eg:
GRANT EXECUTE ON dbo.GetHotels TO USERTEST3
You find that such users can execute + run + use "most" store procedures, but in this case, you have to add specific rights with above grant due to the "table" not being known or resolved until runtime.
So, the above is a way to send a "given" array that you have, but from a general permissions point of view, and that of creating t-sql on the fly - I can't recommend this approach unless you are stuck, and have no other choice.
Edit
Here is a solution that works the same as above, but we don't have to create a SQL statement as a string.
CREATE PROCEDURE [dbo].[GetHotels2]
@IdList nvarchar(max)
AS
BEGIN
SET NOCOUNT ON;
-- create a table from the passed list
declare @List table (ID int)
while charindex(',',@IdList) > 0
begin
insert into @List (ID) values(left(@IDList,charindex(',',@IdList)-1))
set @Idlist = right(@IdList,len(@IdList)-charindex(',',@IdList))
end
insert into @List (ID) values(@IdList)
select * from tblHotels where ID in (select ID from @list)
END
How can i pass an array as a parameter into a stored procedure?
I think you can try these two ways:
- Without a stored procedure: You can try with SqlBulkCopy class.
C# Code:
static void Main(string[] args)
{
Console.WriteLine("Inserting ...");
var userId = 777;
var productIds = new List<int> { 1, 2, 3, 4 };
var dto = new Dictionary<int, List<int>>
{
{ userId, productIds }
};
ExecuteBulkInsert(dto);
// ExecuteProcedure(dto);
Console.WriteLine("Done! ...");
Console.ReadLine();
}
public static void ExecuteBulkInsert( Dictionary<int, List<int>> dto)
{
string connectionString = GetConnectionString();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
DataTable newProducts = CreateDataTable(dto);
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = "dbo.UserProducts";
bulkCopy.WriteToServer(newProducts);
}
}
}
private static DataTable CreateDataTable(Dictionary<int, List<int>> dto)
{
const string IdUserColumnName = "IdUser";
const string IdProductColumnName = "IdProduct";
DataTable table = new DataTable();
table.Columns.Add(new DataColumn(IdUserColumnName, typeof(int)));
table.Columns.Add(new DataColumn(IdProductColumnName, typeof(int)));
foreach (var product in dto)
{
foreach (var productId in product.Value)
table.Rows.Add(product.Key, productId);
}
return table;
}
- With a stored procedure: Try with a table-valued parameter
SQL Code:
CREATE TABLE dbo.UserProducts
(
IdUser INT NOT NULL,
IdProduct INT NOT NULL
);
GO
CREATE TYPE dbo.UserProductsType AS TABLE
(
IdUser INT NOT NULL,
IdUser INT NOT NULL
);
GO
CREATE PROCEDURE dbo.UserProductsInsert
@userProductsType dbo.UserProductsType READONLY
AS
BEGIN
INSERT INTO UserProducts
SELECT * FROM @userProductsType
END
C# Code:
private static void ExecuteProcedure( Dictionary<int, List<int>> dto)
{
string connectionString = GetConnectionString();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "dbo.UserProductsInsert";
command.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = command.Parameters.AddWithValue("@userProductsType", CreateDataTable(dto));
parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "dbo.UserProductsType";
command.ExecuteNonQuery();
}
}
}
Passing an array of parameters to a stored procedure
Use a stored procedure:
EDIT:
A complement for serialize List (or anything else):
List<string> testList = new List<int>();
testList.Add(1);
testList.Add(2);
testList.Add(3);
XmlSerializer xs = new XmlSerializer(typeof(List<int>));
MemoryStream ms = new MemoryStream();
xs.Serialize(ms, testList);
string resultXML = UTF8Encoding.UTF8.GetString(ms.ToArray());
The result (ready to use with XML parameter):
<?xml version="1.0"?>
<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<int>1</int>
<int>2</int>
<int>3</int>
</ArrayOfInt>
ORIGINAL POST:
Passing XML as parameter:
<ids>
<id>1</id>
<id>2</id>
</ids>
CREATE PROCEDURE [dbo].[DeleteAllData]
(
@XMLDoc XML
)
AS
BEGIN
DECLARE @handle INT
EXEC sp_xml_preparedocument @handle OUTPUT, @XMLDoc
DELETE FROM
YOURTABLE
WHERE
YOUR_ID_COLUMN NOT IN (
SELECT * FROM OPENXML (@handle, '/ids/id') WITH (id INT '.')
)
EXEC sp_xml_removedocument @handle
Passing array to a SQL Server Stored Procedure
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;
}
Related Topics
How to Convert a Byte Array to a Hexadecimal String, and Vice Versa
Implementing Inotifypropertychanged - Does a Better Way Exist
The Entity Cannot Be Constructed in a Linq to Entities Query
Sending Email Through Gmail Smtp Server With C#
What Is the C# Using Block and Why Should I Use It
Getting Attributes of Enum'S Value
How to Post Messages to an Sta Thread Running a Message Pump
Difference Between String and String in C#
An Async/Await Example That Causes a Deadlock
How to Turn a C# Object into a Json String in .Net
Generating All Possible Combinations
How to Pass Table Value Parameters to Stored Procedure from .Net Code