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

2021-09-10 00:00:00 sql tsql sql-server

我有一个将多个值传递给存储过程的变量.

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

当我看穿 fidler 时，我看到值像

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;

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

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.

推荐答案

您需要做一些事情来实现这一点，因为您的参数正在获取多个值，您需要创建一个表类型并制作您的存储过程接受该类型的参数.

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.

当您获得包含多个值的 One String 时，拆分函数效果很好，但是当您传递多个值时，您需要做这样的事情....

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....

表格类型

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

执行过程

声明一个该类型的变量并用您的值填充它.

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

结果

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

相关文章