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

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.

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  -- lt -- For testing purpose END   EXECUTE PROC  Declare a variable of that type and populate it with your values    DECLARE  Table TYPENAME     -- lt -- Variable of this TYPE   INSERT INTO  Table                -- lt -- Populating the variable   VALUES  331   222   876   932   EXECUTE mainValues  Table   -- lt -- Stored Procedure Executed    Result                                  value                                       331           222           876           932

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

Examples related to sql

Examples related to sql-server

Examples related to tsql