在存储过程中为同一变量传递多个值

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

我有一个变量,它将多个值传递给存储过程。

When I see through fidler I see values being passed correctly like

当我看到fidler时,我看到正确传递的值就像

    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.

当我测试它时,它不会在t_values表中添加任何值。我检查功能等都工作正常。问题是@ Arg1List。看起来存储过程中没有值。请让我知道如何正确声明@ Arg1List所以它需要多个值,因为它似乎是问题。

3 个解决方案

#1

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

#2

Your stored procedure is designed to accept a single parameter, Arg1List. You can't pass 4 parameters to a procedure that only accepts one.

您的存储过程旨在接受单个参数Arg1List。您不能将4个参数传递给只接受一个参数的过程。

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.

为了使其工作,调用您的过程的代码将需要将您的参数连接成一个不超过3000个字符的单个字符串,并将其作为单个参数传递。

#3

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.

好吧,如果没有对你的确切设置的更大看法,如何调用它以及f_split中发生了什么,那么我很难调试这种情况,因为我基本上只是在猜测。但是,我可以提供一种不涉及UDF的替代解决方案,而是依赖于SQL Server的机制构建...... 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):

下面的proc声明接受一个名为@parmsXML的XML类型参数。这将替换您的@ Arg1List参数。当你执行dbo.mainValues时,你提供@parmsXML参数作为XML节点的字符串而不是逗号分隔的列表(我假设这是你正在做的,但从你的问题来看,它不是很清楚):

<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

只需直接从@parmsXML变量中选择值即可

2. SELECTs the values from the @parmsXML variable into a temp #t_values table

将@parmsXML变量中的值选择为temp #t_values表

3. SELECTs from the #t_values table

来自#t_values表的SELECT

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

在您自己的实现中,您可以摆脱第一步(只需选择....),然后将SELECT INTO更改为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 IF EXISTS然后创建proc然后用上面的@parmsXML设置执行它。

--========================================================================================================================
/* 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