CODEWITHSUNDEEP
sqlsql-servert-sql
J. Davidson
J. Davidson

Reputation: 3317

Passing multiple values for same variable in stored procedure

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.

Upvotes: 2

Views: 13277

Answers (3)

M.Ali
M.Ali

Reputation: 69594

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 ║
╚═══════╝

Upvotes: 2

Bill Stidham
Bill Stidham

Reputation: 1480

Well, without a bigger view of your exact setup, how this is being called and what is happening in f_split it can be hard to debug the situation as I am basically just guessing. I can, however, offer an alternative solution that doesn't involve UDFs but rather relies on SQL Server's build in mechanisms... XML.

The below proc declaration accepts an XML type parameter called @parmsXML. This would replace your @Arg1List parameter. When you EXEC dbo.mainValues, you supply the @parmsXML parameter as a string of XML nodes rather than a comma separated list (I assume this is what you are doing but from your question it is not exactly clear):

<parms>
    <parm>331</parm>
    <parm>222</parm>
    <parm>876</parm>
    <parm>932</parm>
</parms>

The stored proc does this:

  1. Just selects the values from from the @parmsXML variable directly
  2. SELECTs the values from the @parmsXML variable into a temp #t_values table
  3. SELECTs from the #t_values table

In your own implementation, you could get rid of the first step (Just select....), then change the SELECT INTO into a INSERT INTO SELECT.

I have the below script setup so that it will DROP IF EXISTS then CREATE the proc and then EXECUTE it with the @parmsXML setup as above. 

--========================================================================================================================
/* DROP AND RECREATE PROC                                                                                               */
--========================================================================================================================

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'mainValues' 
)
   DROP PROCEDURE dbo.mainValues
GO

CREATE PROCEDURE dbo.mainValues
    @parmsXML XML
AS

--========================================================================================================================
/* INTERPRETER DIRECTIVES                                                                                               */
--========================================================================================================================

SET NOCOUNT ON;    -- Turn off "(N row(s) affected)" messages
SET XACT_ABORT ON; -- Auto ROLLBACK on exception

--========================================================================================================================
/* PARMS PROCESSING                                                                                                     */
--========================================================================================================================

-- select from @parmsXML

RAISERROR('Selecting values directly from @parmsXML', 0, 1);

SELECT Parm = n.x.value('.[1]', 'INT')
FROM @parmsXML.nodes('/parms[1]/parm') n(x)
;

-- insert into

RAISERROR('Inserting @parmsXML values into #t_values', 0, 1);

SELECT Parm = n.x.value('.[1]', 'INT')
INTO #t_values
FROM @parmsXML.nodes('/parms[1]/parm') n(x)
;

-- select from #t_values

RAISERROR('Selecting from #t_values', 0, 1);

SELECT * 
FROM #t_values
;


GO

--========================================================================================================================
/* Example EXEC code runs stored proc with @parmsXML supplied                                                           */
--========================================================================================================================

EXECUTE dbo.mainValues @parmsXML = '
    <parms>
        <parm>331</parm>
        <parm>222</parm>
        <parm>876</parm>
        <parm>932</parm>
    </parms>
    '
GO

Upvotes: 0

Roger Rouse
Roger Rouse

Reputation: 2335

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.

Upvotes: 1

Related Questions