将存储过程的结果保存为变量

Given a stored procedure like the one shown below:

给定如下所示的存储过程:

CREATE PROCEDURE [dbo].[GetBusinessUnitSysNameAndGroupNames] 
    @ModelAfter varchar(100),
    @BusinessUnitSystemName varchar(100) OUT,
    @GroupsName varchar(4000) OUT
AS
BEGIN
    if (@ModelAfter = 'Corporate') 
    BEGIN
        SET @GroupsName = 'Admins'
        SET @BusinessUnitSystemName = 'AcmeOutdoors'
    END 
    else if (@ModelAfter = 'Retailers')
    BEGIN
        SET @GroupsName = 'Sellers'
        SET @BusinessUnitSystemName = 'AcmeShoppers'
    END
END

When I run from the SQL Studio command line:

当我从SQL Studio命令行运行时:

EXEC [dbo].[GetBusinessUnitSysNameAndGroupNames] '~ModelAfter~', 'acmeoutdoors', 'admins'

I just get a result in the message panel like Command(s) completed successfully. But what I would like to see the actual result, not just a success message. Something like shown below(which doesn't work, just my idea).

我只是在消息面板(如命令)中获得成功完成的结果。但我希望看到的是实际的结果，而不仅仅是一个成功的信息。如下所示(这行不通，只是我的想法)。

DECLARE @Result varchar(max)
SET @Result = EXEC [dbo].[GetBusinessUnitSysNameAndGroupNames] '~ModelAfter~', 'acmeoutdoors', 'admins'
PRINT @Result

1 个解决方案

#1

Returning Data by Using OUTPUT Parameters

If you specify the OUTPUT keyword for a parameter in the procedure definition, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the OUTPUT keyword when executing the stored procedure.

如果在过程定义中为参数指定OUTPUT关键字，则存储过程可以在存储过程退出时将参数的当前值返回给调用程序。要将参数的值保存到可以在调用程序中使用的变量中，调用程序在执行存储过程时必须使用OUTPUT关键字。

DECLARE @Result1 varchar(max), @Result2, varchar(max)
EXEC [dbo].[GetBusinessUnitSysNameAndGroupNames] 'Corporate', @Result1 OUT, @Result2 OUT

PRINT @Result1
PRINT @Result2