問題描述

我有一個將多個值傳遞給存儲過程的變量.

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

這篇關于在存儲過程中為同一變量傳遞多個值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！