Stored Proceedure IN statement parameters

Spotz 1 Reputation point
2023-01-01T00:22:47.203+00:00

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

Azure SQL Database
{count} votes

2 answers

Sort by: Most helpful
  1. TechLife 241 Reputation points
    2023-01-01T00:29:52.167+00:00

    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.

    No comments

  2. Dan Guzman 6,931 Reputation points
    2023-01-01T12:39:25.93+00:00

    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  
    
    No comments