T-SQL 获取存储过程的 SELECTed 值 [英] T-SQL get SELECTed value of stored procedure

问题描述

在 T-SQL 中，这是允许的:

In T-SQL, this is allowed:

DECLARE @SelectedValue int
SELECT @SelectedValue = MyIntField FROM MyTable WHERE MyPrimaryKeyField = 1

因此，可以获取 SELECT 的值并将其填充到变量中(显然，前提是它是标量).

So, it's possible to get the value of a SELECT and stuff it in a variable (provided it's scalar, obviously).

如果我将相同的选择逻辑放在存储过程中:

If I put the same select logic in a stored procedure:

CREATE PROCEDURE GetMyInt
AS
SELECT MyIntField FROM MyTable WHERE MyPrimaryKeyField = 1

我可以获取这个存储过程的输出并将其填充到变量中吗?

Can I get the output of this stored procedure and stuff it in a variable?

类似于:

DECLARE @SelectedValue int
SELECT @SelectedValue = EXEC GetMyInt

(我知道上面的语法是不允许的，因为我试过了！)

(I know the syntax above is not allowed because I tried it!)

推荐答案

可以使用三种方式:RETURN 值、OUTPUT 参数和结果集

there are three ways you can use: the RETURN value, and OUTPUT parameter and a result set

另外，请注意是否使用以下模式:SELECT @Variable=column FROM table ...

ALSO, watch out if you use the pattern: SELECT @Variable=column FROM table ...

如果查询返回多行，您的@Variable 将只包含查询返回的最后一行的值.

if there are multiple rows returned from the query, your @Variable will only contain the value from the last row returned by the query.

返回值
因为您的查询返回一个 int 字段，至少基于您如何命名它.你可以使用这个技巧:

RETURN VALUE
since your query returns an int field, at least based on how you named it. you can use this trick:

CREATE PROCEDURE GetMyInt
( @Param int)
AS
DECLARE @ReturnValue int

SELECT @ReturnValue=MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN @ReturnValue
GO

现在调用你的程序，如:

and now call your procedure like:

DECLARE @SelectedValue int
       ,@Param         int
SET @Param=1
EXEC @SelectedValue = GetMyInt @Param
PRINT @SelectedValue

这仅适用于 INT，因为 RETURN 只能返回单个 int 值，而 null 将转换为零.

this will only work for INTs, because RETURN can only return a single int value and nulls are converted to a zero.

输出参数
您可以使用输出参数:

OUTPUT PARAMETER
you can use an output parameter:

CREATE PROCEDURE GetMyInt
( @Param     int
 ,@OutValue  int OUTPUT)
AS
SELECT @OutValue=MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN 0
GO

现在调用你的程序，如:

and now call your procedure like:

DECLARE @SelectedValue int
       ,@Param         int
SET @Param=1
EXEC GetMyInt @Param, @SelectedValue OUTPUT
PRINT @SelectedValue 

输出参数只能返回一个值，但可以是任何数据类型

Output parameters can only return one value, but can be any data type

结果集对于结果集，请执行如下程序:

RESULT SET for a result set make the procedure like:

CREATE PROCEDURE GetMyInt
( @Param     int)
AS
SELECT MyIntField FROM MyTable WHERE MyPrimaryKeyField = @Param
RETURN 0
GO

像这样使用:

DECLARE @ResultSet table (SelectedValue int)
DECLARE @Param int
SET @Param=1
INSERT INTO @ResultSet (SelectedValue)
    EXEC GetMyInt @Param
SELECT * FROM @ResultSet 

结果集可以有任意数据类型的多行多列

result sets can have many rows and many columns of any data type

这篇关于T-SQL 获取存储过程的 SELECTed 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！