How to assign an exec result to a sql variable?

How do you assign the result of an exec call to a variable in SQL? I have a stored proc called up_GetBusinessDay, which returns a single date.

Can you do something like this:

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

Solution

I always use the return value to pass back error status. If you need to pass back one value I'd use an output parameter.

Here's a sample 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

Here's an example of calling the stored procedure, with an OUTPUT parameter:

DECLARE @OutputParameter datetime
       ,@ReturnValue     int

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

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

Output:

0
2010-01-01 00:00:00.000