TSQL 存储过程检索变量

本文介绍了TSQL 存储过程检索变量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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