How Can I Assign the Results of an SQL `exec` Command to a Variable?

Using Output Parameters to Assign Exec Results to SQL Variables

In SQL, the exec command is commonly used to execute stored procedures and functions. While you cannot directly assign the result of an exec call to a variable using an expression like the one you suggested ("exec @PreviousBusinessDay = dbo.up_GetBusinessDay @Date, -1"), there are alternative methods to achieve this functionality.

One preferred approach involves using output parameters. Output parameters allow you to pass a variable as an input, but also have it updated with a value returned from the stored procedure or function. Here's how you can do it:

Create a stored procedure with an output parameter:

CREATE PROCEDURE YourStoredProcedure(
    @Param1 int,
    @Param2 varchar(5),
    @Param3 datetime OUTPUT
)
AS
    IF ISNULL(@Param1, 0) > 5
    BEGIN
        SET @Param3 = GETDATE()
    END
    ELSE
    BEGIN
        SET @Param3 = '1/1/2010'
    END
    
    RETURN 0
GO

In this example, the @Param3 parameter is declared as an output parameter. It will be updated with the value returned by the stored procedure.

Calling the stored procedure using an output parameter:

DECLARE @OutputParameter datetime
       ,@ReturnValue     int

EXEC @ReturnValue = YourStoredProcedure 1, NULL, @OutputParameter OUTPUT

PRINT @ReturnValue
PRINT CONVERT(char(23), @OutputParameter, 121)

In this example, the @OutputParameter variable is declared and passed as an output parameter to the stored procedure. After the exec call, the @OutputParameter variable will contain the result returned by the stored procedure, which is then printed to the console.

Output:

0
2010-01-01 00:00:00.000

Using output parameters provides a robust and efficient way to pass values between stored procedures and client applications in SQL.

The above is the detailed content of How Can I Assign the Results of an SQL `exec` Command to a Variable?. For more information, please follow other related articles on the PHP Chinese website!