How to assign an exec result to a sql variable?

Question

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

Answer 1

You can use the return value to pass back error status. But, if you want to pass back one value then use an output parameter.

Here's is an example for sample stored procedure, with an OUTPUT parameter:

Query:

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

You can also try calling to the stored procedure, with an OUTPUT parameter like this:

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

Enroll yourself in the SQL server certification to learn in-depth about SQL statements, queries and become proficient in SQL. 

Answer 2

Unfortunately, you cannot directly assign a result using exec statement to variable.

For that use outputparameter,

CREATE PROCEDURE up_GetBusinessDay

    @Date DATE,

    @DaysToAdd INT,

    @BusinessDay DATE OUTPUT

AS

BEGIN

    -- ... Your existing logic to calculate the business day ...

    SET @BusinessDay = -- Calculated business day

END

DECLARE @PreviousBusinessDay DATE; EXEC dbo.up_GetBusinessDay @Date, -1, @PreviousBusinessDay OUTPUT;