Stored Proceedure IN statement parameters

Question

G'day,

Could I please get some guidance on how the pass in multiple values for a Store Procedure with and IN statement. The only examples I have found require that each value is passed in to a separate variable and then concatenated into another variable before use in the IN statement.

Thanks

Answer 1

You can pass multiple values to a stored procedure using an IN clause in the stored procedure's parameter list.

For example, if you want to pass in a list of integers to a stored procedure, you could define the stored procedure as follows:

CREATE PROCEDURE MyProc  
    @intList INT[],  
AS  
BEGIN  
    SELECT * FROM MyTable WHERE MyColumn IN @intList  
END  

To call the stored procedure and pass in the list of integers, you can use the following code:

DECLARE @intList INT[]  
SET @intList = (1, 2, 3, 4, 5)  
EXEC MyProc @intList  

Note that the list of integers is passed in as an array. You can also use a table-valued parameter to pass in a list of values, but this requires creating a user-defined table type and using it as the parameter data type.

Answer 2

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