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.
This question is related to
sql
sql-server
tsql
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.
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 ║ ╚═══════╝
Source: Stackoverflow.com