TSQL 存储过程检索变量 [英] TSQL Stored Procedure retrieve variables

问题描述

我想知道是否可以使用另一个存储过程检索存储过程的结果.

I was wondering if it's possible to retrieve the results of a stored procedure using another stored procedure.

我有以下作为测试示例

第一个存储过程旨在返回两个值

The first stored procedure is intended to return two values

if(object_id('sp_function01')) is not null
    drop procedure sp_function01
go

create procedure sp_function01
as

    declare @lv_res01 char(20)
    declare @lv_res02 char(20)

    set @lv_res01 = 'fn01 result 01'
    set @lv_res02 = 'fn01 result 02'

    select @lv_res01, @lv_res02

go

然后第二个旨在检索这些值并利用它们

Then the second is intended to retrieve those values and utilize them

if(object_id('sp_function02')) is not null
    drop procedure sp_function02
go

create procedure sp_function02
as

    declare @lv_var01 char(20)
    declare @lv_res01 char(20)
    declare @lv_res02 char(20)

    set @lv_var01 = 'variable 01'

    exec sp_function01

    -- catch variables from this function
    -- into lv_res01 and lv_res02

    select @lv_var01, @lv_res01, @lv_res02

go

然而 lv_res01 和 lv_res02 的值为 NULL，我不知道如何实际捕捉它们

Yet the values for lv_res01 and lv_res02 are NULL and I'm not sure how to actually catch them

推荐答案

是这样的:

第一步:

create procedure sp_function01
@lv_res01 char(20) OUTPUT,
@lv_res02 char(20) OUTPUT
as

    set @lv_res01 = 'fn01 result 01'
    set @lv_res02 = 'fn01 result 02'

    select @lv_res01, @lv_res02

go

第二个程序:

create procedure sp_function02
as

    declare @lv_var01 char(20)
    declare @lv_res01 char(20)
    declare @lv_res02 char(20)

    set @lv_var01 = 'variable 01'

    exec sp_function01 @lv_res01 OUTPUT, @lv_res02 OUTPUT

    -- catch variables from this function
    -- into lv_res01 and lv_res02

    select @lv_var01, @lv_res01, @lv_res02

go

单独调用 sp_function01(大致相同，您只需要声明所有输出参数并将它们从另一个存储过程内部或外部传递给 proc):

Calling sp_function01 alone (it's more or less same you just need to declare all output parameters and pass them to proc whether from inside another stored procedure or outside of it):

DECLARE @lv_res01 char(20)
DECLARE @lv_res02 char(20)

exec sp_function01 @lv_res01 OUTPUT, @lv_res02 OUTPUT

这篇关于TSQL 存储过程检索变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！