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, ''
+ REPLACE(@List, @Delimiter, '') + '').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.
Passing an array of parameters to a stored procedure
Use a stored procedure:
EDIT:
A complement for serialize List (or anything else):
List testList = new List();
testList.Add(1);
testList.Add(2);
testList.Add(3);
XmlSerializer xs = new XmlSerializer(typeof(List));
MemoryStream ms = new MemoryStream();
xs.Serialize(ms, testList);
string resultXML = UTF8Encoding.UTF8.GetString(ms.ToArray());
The result (ready to use with XML parameter):
1
2
3
ORIGINAL POST:
Passing XML as parameter:
1
2
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
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 { 1, 2, 3, 4 };
var dto = new Dictionary>
{
{ userId, productIds }
};
ExecuteBulkInsert(dto);
// ExecuteProcedure(dto);
Console.WriteLine("Done! ...");
Console.ReadLine();
}
public static void ExecuteBulkInsert( Dictionary> 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> 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> 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();
}
}
}
How to bind an Array Parameter (Stored Procedure) via Native ODBC
Digging deeper, I found out that the first ODBC API call failing is actually the binding call for the TVP column / parameter:
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, arr_size, 0, tvp_name, 0, &cb);
This call returns SQL_ERROR and checking the diagnostic record issued by this gave me the following error:
HY004 [Microsoft][ODBC SQL Server Driver]Invalid SQL data type
This specific issue was asked here already but sadly remained unsolved. Ultimately using an outdated ODBC driver was the cause of this whole problem. See my answer on another question for more details on how to fix that: https://stackoverflow.com/a/47113255/2334932
Then two points brought up by @A.K. in his comment finally solved the issue:
1. Parameter Length
The last value passed to SQLBindParameter, the parameter length or cb here, needed to have actual amount of available rows rather than SQL_NTS, as it is used as input parameter here.
2. Passing the parameter name is not necessary as they are bound by position
I think it will work with or without, but specifying the name of the TVP here is actually not necessary and can be left out.
So changing the third SQLBindParameter call to this fixed the rest of the issue(s):
cb = arr_size;
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, arr_size, 0, NULL, 0, &cb);
Full working code
void UpdateCharacterNationTestTVP()
{
unsigned int old_nation_id = 2;
unsigned int new_nation_id = 4;
unsigned int excluded_character_ids[] = {24, 36};
SQLLEN arr_size = ARRAYSIZE(excluded_character_ids);
SQLINTEGER cb = arr_size; // Needs to have the actual amount of available rows
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0, &old_nation_id, 0, NULL);
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0, &new_nation_id, 0, NULL);
SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_SS_TABLE, arr_size, 0, NULL, 0, &cb); // Does not need the name of the TVP
// Super scary binding stuff tvp requires you to do
SQLINTEGER cb_rows[] = {SQL_NTS, SQL_NTS};
SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)3, SQL_IS_INTEGER); // focusing the third parmeter (the TVP one) for the call(s) below
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, arr_size /*or 0*/, 0, excluded_character_ids, sizeof(UINT), cb_rows); // binding of the actual array in a column styled fashion here
SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)0, SQL_IS_INTEGER); // resetting the focus
SQLRETURN res = SQLExecDirect(hstmt, (SQLCHAR*)"{call dbo.Update_Character_Nation(?,?,?)}", SQL_NTS);
if (res != SQL_SUCCESS && res != SQL_SUCCESS_WITH_INFO)
{
printf("Error during query execution: %hd\n", res);
ProcessLogs(SQL_HANDLE_STMT, hstmt);
}
}
How to pass an array or list to a stored procedure in PostgreSQL
Function with comma separated input value and other value and prepare dynamic query where few input param (if provided) and use regexp_split_to_table for splitting comma separated value into list. If null or empty string ('') is used as input value then related column name isn't used in searching criteria.
-- PostgreSQL (v14)
-- Function: public.rpt_member_info(character varying, character varying, character varying, character varying, character varying)
DROP FUNCTION IF EXISTS public.rpt_member_info(character varying, character varying, character varying, character varying, character varying);
CREATE OR REPLACE FUNCTION public.rpt_member_info(
IN p_officeInfoId CHARACTER VARYING,
IN p_projectInfoId CHARACTER VARYING,
IN p_memberInfoId CHARACTER VARYING,
IN p_fromDate CHARACTER VARYING,
IN p_toDate CHARACTER VARYING,
OUT member_id BIGINT,
out office_info_id bigint,
out project_info_id bigint,
OUT member_no CHARACTER VARYING,
OUT member_name CHARACTER VARYING,
OUT membership_date TIMESTAMP WITHOUT TIME ZONE)
RETURNS SETOF record AS
$BODY$
-- declare local variables
DECLARE v_prepareQuery VARCHAR(21840) DEFAULT '';
v_officeInfo VARCHAR(150) DEFAULT '';
v_projectInfo VARCHAR(150) DEFAULT '';
v_memberInfo VARCHAR(150) DEFAULT '';
refcur refcursor default 'test';
BEGIN
/**************************History**************************/
-- Name : rpt_member_info
-- Created Date (dd/MM/yyyy): 01/02/2022
-- Created By : Rahul Biswas
-- Reason :
-- Execute : SELECT * FROM rpt_member_info('101', '1', '1', '2022-01-01', '2022-01-31');
/***********************************************************/
-- prepare variable data based on given input
IF(p_officeInfoId != '' AND p_officeInfoId != 'null' AND p_officeInfoId IS NOT NULL)
THEN
v_officeInfo = CONCAT(' AND mi.office_info_id = ANY(SELECT CAST(regexp_split_to_table(''', p_officeInfoId, ''',''', ', '') AS BIGINT))', CHR(10));
END IF;
IF(p_projectInfoId != '' AND p_projectInfoId != 'null' AND p_projectInfoId IS NOT NULL)
THEN
v_projectInfo = CONCAT(' AND mi.project_info_id = ', p_projectInfoId, CHR(10));
END IF;
IF(p_memberInfoId != '' AND p_memberInfoId != 'null' AND p_memberInfoId IS NOT NULL)
THEN
v_memberInfo = CONCAT(' AND mi.id = ', p_memberInfoId, CHR(10));
END IF;
-- prepare query
v_prepareQuery := CONCAT('SELECT mi.id member_id
, mi.office_info_id
, mi.project_info_id
, mi.member_no
, mi.member_name
, mi.membership_date
FROM member_info mi
WHERE 1 = 1', CHR(10)
, v_officeInfo
, v_projectInfo
, v_memberInfo
, 'AND mi.membership_date BETWEEN ', '''', p_fromDate, '''', ' AND ', '''', p_toDate, '''', ';');
RETURN QUERY EXECUTE v_prepareQuery;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
Execute this function
SELECT * FROM rpt_member_info('101, 102', '1', '', '2022-01-01', '2022-01-31');
Please check this url https://dbfiddle.uk/?rdbms=postgres_14&fiddle=a38347cb181d0347a06ba6304e9448ef
If don't use dynamic query and supply comma separated value as input then use the below function
-- Function: public.rpt_member_info(character varying, bigint, TIMESTAMP WITHOUT TIME ZONE, bigint, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITHOUT TIME ZONE)
DROP FUNCTION IF EXISTS public.rpt_member_info(character varying, bigint, TIMESTAMP WITHOUT TIME ZONE, TIMESTAMP WITHOUT TIME ZONE);
CREATE OR REPLACE FUNCTION public.rpt_member_info(
IN p_officeInfoId CHARACTER VARYING,
IN p_projectInfoId BIGINT,
IN p_fromDate TIMESTAMP WITHOUT TIME ZONE,
IN p_toDate TIMESTAMP WITHOUT TIME ZONE,
OUT member_id BIGINT,
out office_info_id bigint,
out project_info_id bigint,
OUT member_no CHARACTER VARYING,
OUT member_name CHARACTER VARYING,
OUT membership_date TIMESTAMP WITHOUT TIME ZONE)
RETURNS SETOF record AS
$BODY$
-- declare local variables
DECLARE v_prepareQuery VARCHAR(21840) DEFAULT '';
v_officeInfo VARCHAR(150) DEFAULT '';
v_projectInfo VARCHAR(150) DEFAULT '';
v_memberInfo VARCHAR(150) DEFAULT '';
refcur refcursor default 'test';
BEGIN
/**************************History**************************/
-- Name : rpt_member_info
-- Created Date (dd/MM/yyyy): 01/02/2022
-- Created By : Rahul Biswas
-- Reason :
-- Execute : SELECT * FROM rpt_member_info('101, 102', 1, '2022-01-01', '2022-01-31');
/***********************************************************/
-- execute query
RETURN QUERY SELECT mi.id member_id
, mi.office_info_id
, mi.project_info_id
, mi.member_no
, mi.member_name
, mi.membership_date
FROM member_info mi
WHERE mi.office_info_id = ANY(SELECT CAST(regexp_split_to_table(p_officeInfoId, ',') AS BIGINT))
AND mi.project_info_id = p_projectInfoId
AND mi.membership_date BETWEEN p_fromDate AND p_toDate;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
Execute the function
SELECT * FROM rpt_member_info('101, 102', 1, '2022-01-01', '2022-01-31');
Please use this url https://dbfiddle.uk/?rdbms=postgres_14&fiddle=ee5b70bbb8fbeacbb9fad280cc775b58
N.B.: In lower version of Postgresql function and stored procedure are behave like same.
Related Topics
Copy Tables from One Database to Another in SQL Server
Creating a "Numbers Table" in MySQL
Is Having an 'Or' in an Inner Join Condition a Bad Idea
SQL Standard to Escape Column Names
SQL Query: Delete All Records from the Table Except Latest N
Alter a MySQL Column to Be Auto_Increment
What's the Difference Between Varchar and Char
Decision Between Storing Lookup Table Id'S or Pure Data
Concatenate Values Based on Id
Split Column into Multiple Rows in Postgres
Restore Table Structure from Frm and Ibd Files
T-SQL Datetime Rounded to Nearest Minute and Nearest Hours With Using Functions
SQL Server Check Case-Sensitivity
MySQL Query to Select Data from Last Week
List of Special Characters For SQL Like Clause