How to Assign the Result of an EXEC Call to a SQL Variable?

Assigning Exec Results to SQL Variables

Question:
How can you assign the result of an exec call to a variable in SQL? For instance, if you have a stored procedure named up_GetBusinessDay that returns a single date, can you assign the result using a statement like:

exec @PreviousBusinessDay = dbo.up_GetBusinessDay @Date, -1

Answer:

Utilizing an output parameter is a preferred approach rather than assigning the result directly to a variable. Here's an example of creating 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

To call this stored procedure and use the output parameter:

DECLARE @OutputParameter datetime
       ,@ReturnValue     int

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

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

This will output:

0
2010-01-01 00:00:00.000

The above is the detailed content of How to Assign the Result of an EXEC Call to a SQL Variable?. For more information, please follow other related articles on the PHP Chinese website!