将变量作为参数传递给存储过程 [英] Pass variable as parameter on a Stored Procedure

问题描述

我有一个带有 SELECT 语句和两个参数的存储过程.我想从另一个声明了变量的存储过程调用这个存储过程，并将这个变量用作参数之一.

I have a stored procedure with a SELECT statement and two parameters. I want to call this stored procedure from another one that has a variable declared, and use this variable as one of the parameters.

当我尝试这个时:

EXEC [dbo].[Testo] @cd_order = 23, @cd_substep = 33

结果返回了一些行，但是当我尝试这样做时:

It returns some rows as result, but when I try this:

set @temp_var1 = ( Select cd_substep FROM ....Where...)

EXEC [dbo].[Testo] @cd_order = 23, @cd_substep = @temp_var1

结果为空.

该过程将填充一个表变量:

The procedure will populate a table variable:

INSERT INTO @Var1Table EXEC [dbo].[Testo] 23, @cd_substep 

它仅在我使用静态值时才有效.如何使用变量作为参数?

It's working only when I use a static value. How do I use a variable as a parameter?

推荐答案

这不是您代码中的问题，因为我可以简单地复制您拥有的内容并且可以正常工作:

This is not a problem in your code, as I can simply copy what you have and it works:

DECLARE @return_value int
DECLARE @temp_var1 int

SET @temp_var1 = (SELECT 65)

EXEC    @return_value = [dbo].[GetRecordLog]
        @Action = N'All',
        @EntityID = 1,
        @RecordID = @temp_var1

SELECT  'Return Value' = @return_value

注意我的 @temp_var1 的值为 65.然后我得到与此匹配的行，我知道代码与您的不完全相同，但概念保持不变.问题不在于您的代码，而在于 @temp_var1 的值.

Notice my @temp_var1 has a value of 65. I then get rows which match this, I know the code is not exactly the same as yours but the concept remains the same. The issue is not with your code but with the value that @temp_var1 has.

这篇关于将变量作为参数传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！