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
The typical pattern in this situation is to pass the elements in a comma delimited list, and then in SQL split that out into a table you can use. Most people usually create a specified function for doing this like:
INSERT INTO <SomeTempTable>
SELECT item FROM dbo.SplitCommaString(@myParameter)
And then you can use it in other queries.
No, arrays/lists can't be passed to SQL Server directly.
The following options are available:
- Passing a comma-delimited list and then having a function in SQL split the list. The comma delimited list will most likely be passed as an Nvarchar()
- Pass xml and have a function in SQL Server parse the XML for each value in the list
- Use the new defined User Defined table type (SQL 2008)
- Dynamically build the SQL and pass in the raw list as "1,2,3,4" and build the SQL statement. This is prone to SQL injection attacks, but it will work.