Problem
How do you pass an array into a stored procedure in SQL Server?
I have a list of employees, for example. I want to make a table out of this list and attach it to another table. The list of employees, on the other hand, should be supplied as a parameter from C#.
Asked by Sergey
Solution #1
Create the following two objects in your database first:
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 write the following code in C#:
// 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
}
I would still advocate a split function over XML if you’re running SQL Server 2005. Create a function first:
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
Your stored procedure might now simply be:
CREATE PROCEDURE dbo.DoSomethingWithEmployees
@List VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ',');
END
GO
And all you have to do in your C# code is send the list as ‘1,2,3,12’…
When compared to other solutions such as XML and string splitting, I find that passing over table valued arguments simplifies the maintainability of a solution that employs it and often improves performance.
The inputs are well-defined (no one needs to guess whether the delimiter is a comma or a semi-colon), and we don’t have any hidden dependencies on other processing functions that can’t be seen without looking at the stored procedure’s code.
When compared to systems that use a user-defined XML schema instead of UDTs, this has a similar amount of stages but is significantly easier to manage, maintain, and read in my experience.
Answered by Aaron Bertrand
Solution #2
According to my experience, a tricky and nice solution to this problem can be found by building a delimited expression from the employeeIDs. You should only use string expressions such as ‘;123;434;365;’, where 123, 434, and 365 are employeeIDs. You can get your chosen records by running the procedure below and giving this phrase to it. This query can easily be joined to “another table.” This approach works with all SQL Server versions. It is also a much faster and more optimal option than using table variables or temp tables.
CREATE PROCEDURE dbo.DoSomethingOnSomeEmployees @List AS varchar(max)
AS
BEGIN
SELECT EmployeeID
FROM EmployeesTable
-- inner join AnotherTable on ...
where @List like '%;'+cast(employeeID as varchar(20))+';%'
END
GO
Answered by Hamed Nazaktabar
Solution #3
For your stored process, use a table-valued parameter.
You’ll add the parameter with the data type SqlDb.Structured when passing it in from C#.
See here: http://msdn.microsoft.com/en-us/library/bb675163.aspx
Example:
// Assumes connection is an open SqlConnection object.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories =
CategoriesDataTable.GetChanges(DataRowState.Added);
// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand(
"usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
"@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
// Execute the command.
insertCommand.ExecuteNonQuery();
}
Answered by Levi W
Solution #4
It should be given as an XML argument.
Edit: to give you an idea, here’s some code from my project:
CREATE PROCEDURE [dbo].[GetArrivalsReport]
@DateTimeFrom AS DATETIME,
@DateTimeTo AS DATETIME,
@HostIds AS XML(xsdArrayOfULong)
AS
BEGIN
DECLARE @hosts TABLE (HostId BIGINT)
INSERT INTO @hosts
SELECT arrayOfUlong.HostId.value('.','bigint') data
FROM @HostIds.nodes('/arrayOfUlong/u') as arrayOfUlong(HostId)
The temp table can then be used to link your tables. To guarantee data integrity, we designed arrayOfUlong as a built-in XML structure, but you don’t have to. I’d suggest utilizing it, so here’s some code to ensure you always get an XML with longs.
IF NOT EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'xsdArrayOfULong')
BEGIN
CREATE XML SCHEMA COLLECTION [dbo].[xsdArrayOfULong]
AS N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="arrayOfUlong">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded"
name="u"
type="xs:unsignedLong" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>';
END
GO
Answered by Fedor Hajdu
Solution #5
Context, such as the array’s size and complexity, is always crucial. Several of the solutions provided here are suitable for small to medium-sized lists, while additional clarifications are required:
With that stated, if the data you’re working with is enormous, or isn’t very large but is continually growing, the IEnumerable TVP method is the best option because it streams the data to SQL Server (like the DataTable method), but it doesn’t require any memory duplication (unlike any of the other methods). In this answer, I provided an example of SQL and C# code:
T-SQL Dictionary to T-SQL Stored Procedure
Answered by Solomon Rutzky
Post is based on https://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure