A few of the options to pass a list of values as a stored procedure include a comma-separated list, json, and a table-valued parameter (TVP). Proc examples for each of these techniques:
CREATE OR ALTER PROCEDURE dbo.usp_CsvExample
@intList varchar(MAX)
AS
SELECT YourColumnName
FROM dbo.YourTable
WHERE YourColumnName IN (
SELECT value
FROM STRING_SPLIT(@intList, ',')
);
GO
CREATE OR ALTER PROCEDURE dbo.usp_JsonArrayExample
@intList varchar(MAX)
AS
SELECT YourColumnName
FROM dbo.YourTable
WHERE YourColumnName IN (
SELECT value
FROM OPENJSON(@intList)
);
GO
--Create table type for TVP
CREATE TYPE IntListTableType AS TABLE(
IntValue int NOT NULL PRIMARY KEY
);
GO
CREATE OR ALTER PROCEDURE dbo.usp_TVPExample
@intList IntListTableType READONLY
AS
SELECT YourColumnName
FROM dbo.YourTable
WHERE YourColumnName IN (
SELECT IntValue FROM @IntList
);
GO
Below are sample invocations for the commas-separated list and json paramters.
--comma-separated list
EXEC dbo.usp_CsvExample @intList = '1,2,3';
GO
--json array
EXEC dbo.usp_JsonArrayExample @intList = '[1,2,3]';
GO
A TVP in T-SQL necessitates one declare the table type variable and inserting rows for the list. A TVP in application code may be passed as a parameter of type Structured
with an object value such as a DataTable or List<SqlDataRecord>
.
--TVP
DECLARE @intList IntListTableType;
--insert values a rows
INSERT INTO @intList VALUES (1),(2),(3);
EXEC dbo.usp_TVPExample @intList = @intList
GO