Passing multiple values for same variable in stored procedure

Question

I have a variable that passes multiple values to stored procedure    When I see through fidler I see values being passed correctly like       arg1 331     arg1 222     arg1 876     arg1 932   In my stored procedure I am reading as      procedure mainValues        Arg1List     nvarchar 3000      as begin   --Temp table to store split values   declare  tmp values table     value nvarchar 255  not null          --function splitting values     insert into  tmp values     select   from f split  Arg1List             --inserting in table value column is int          insert into t values      value       select    b value   from  tmp values b    When I test it  it doesn t add any values in t values table  I checked the function etc  are all working fine  The problem is  Arg1List  It looks like stored procedure has no values in it  Please let me know how to declare  Arg1List properly so it takes multiple values as it seems to be the problem

User · Answer

You will need to do a couple of things to get this going, since your parameter is getting multiple values you need to create a Table Type and make your store procedure accept a parameter of that type.

Split Function Works Great when you are getting One String containing multiple values but when you are passing Multiple values you need to do something like this....

TABLE TYPE

CREATE TYPE dbo.TYPENAME AS TABLE   (     arg int    )  GO

Stored Procedure to Accept That Type Param

 CREATE PROCEDURE mainValues   @TableParam TYPENAME READONLY  AS     BEGIN     SET NOCOUNT ON;   --Temp table to store split values   declare @tmp_values table (   value nvarchar(255) not null);        --function splitting values     INSERT INTO @tmp_values (value)    SELECT arg FROM @TableParam      SELECT * FROM @tmp_values  --<-- For testing purpose END

EXECUTE PROC

Declare a variable of that type and populate it with your values.

 DECLARE @Table TYPENAME     --<-- Variable of this TYPE   INSERT INTO @Table                --<-- Populating the variable   VALUES (331),(222),(876),(932)  EXECUTE mainValues @Table   --<-- Stored Procedure Executed

Result

╔═══════╗ ║ value ║ ╠═══════╣ ║   331 ║ ║   222 ║ ║   876 ║ ║   932 ║ ╚═══════╝

User · Answer

Your stored procedure is designed to accept a single parameter  Arg1List   You can t pass 4 parameters to a procedure that only accepts one   To make it work  the code that calls your procedure will need to concatenate your parameters into a single string of no more than 3000 characters and pass it in as a single parameter

[sql] Passing multiple values for same variable in stored procedure

Examples related to sql

Examples related to sql-server

Examples related to tsql