Geek asks and answers

Passing multiple values ​​for the same variable in a stored procedure

I have a variable that passes multiple values ​​to a stored procedure.

When I see through fidler, I see that the values ​​are passed correctly, as

    arg1=331
    arg1=222
    arg1=876
    arg1=932

In my stored procedure I read 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 does not add any values ​​to the t_values ​​table. I checked the function, etc. Everything is working fine. The problem is @ Arg1List. It looks like the stored procedure has no meaning in it. Please let me know how to declare @ Arg1List correctly, so it takes a few values ​​as this seems like a problem.

+2
source share
3 answers

, , , , .

. , One String, , , - ....

TABLE TYPE

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


 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 @Table TYPENAME     --<-- Variable of this TYPE

 INSERT INTO @Table                --<-- Populating the variable 
 VALUES (331),(222),(876),(932)

EXECUTE mainValues @Table   --<-- Stored Procedure Executed 


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

Arg1List. 4 , .

, , , 3000 .

+1

, , f_split, , . , UDF, SQL Server ... XML.

proc XML, @parmsXML. @Arg1List. EXEC dbo.mainValues, @parmsXML XML, , ( , , , ):

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

proc :

  • @parmsXML
  • SELECT values ​​from @parmsXML variable to temp table #t_values
  • SELECT from table #t_values

In your own implementation, you can get rid of the first step (Just select ...), and then change SELECT INTO to INSERT INTO SELECT.

I have the setting below the script so that it has DROP IF EXISTS, then CREATE proc, and then EXECUTE it with @parmsXML setting, 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
0
source

More articles:


All Articles