我有一个将多个值传递给存储过程的变量.
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 ║
╚═══════╝
这篇关于在存储过程中为同一变量传递多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!