Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-30 : 08:39:05

raj writes "Using SQL Server 2000

I run 20 stored procedures as steps in a job scheduled to run each day through the SQL Server Agent.
A source table (dbo.Application)is required to be rebuilt each day and it is important current data exists for jobs to be successful.

Therefore the first step is

CREATE PROCEDURE [sproc_Verify] AS
DECLARE @Error INT
SET @Error = 0
IF EXISTS(SELECT * FROM dbo.APPLICATION)OR (SELECT MAX(CommencedDate)AS d FROM dbo.APPLICATION) < GETDATE() - 1
BEGIN
SET @Error =1
END
RETURN( @Error)
GO

What I want to happen if the RETURN = 1 is for the entire job to fail and send me an email.
The job is succeeding wheather or not the RETURN is 0 or 1.

Cant seem to find an answer anywhere.
I would be greatfull for any assistance."

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-30 : 11:48:46

In your job window, you need to do something like this:

DECLARE @retvalue INT

EXEC @retvalue = sproc_Verify

IF @retvalue = 1
BEGIN
RAISERROR('Error on step1',16,1)
END

Make sure you have the job setup to fail on error.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources