Send a return value from an sp to a job?

[takaoki]

Hello,

Is there a way to send a return value from a stored procedure (which is called from a job step) back to the job?

Basically, I want to be able to send a "failure" code back to the job from the stored procedure that will fail the job step.

Is this possible?

Thanks

 

[bborissov]

SP can returns only integers.
If you want to return something else you should use otuput parameters. But you could use integers to return "Success" or "Faulre"

CREATE PROCEDURE MyTestSP
 @SomeParameter int
AS
  BEGIN
     DECLARE @RetVal int
     IF @SomeParameter < 1
        BEGIN
           --- The SP returns SUCCESS
           SET @RetVal = 1
        END
     ELSE
        BEGIN
           --- The SP returns Error
           SET @RetVal = -1
        END
    RETURN @RetVal
  END
GO


DECLARE @Result int
EXEC @REsult = MyTestSP -10
IF @REsult = 1
   print 'Success'
ELSE
   print 'Error'

EXEC @REsult = MyTestSP 10
IF @REsult = 1
   print 'Success'
ELSE
   print 'Error'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[takaoki]

Thanks... but how will the calling job recognize that a failure code has been sent? I need to communicate with the job step... if possible.

 

[bborissov]

What job is that?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[takaoki]

My sql server job

 

[bborissov]

Really!?!
(sorry, I can't resist)


Created how?
With SSIS maybe.



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.