0 votes
1 view
in SQL by (22.4k points)

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

1 Answer

0 votes
by (40.3k points)

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

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...