Error Handling in Stored Procedures

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 Development (2000)

Error Handling in Stored Procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-28 : 07:46:50

Adimulam Srinivasa Rao writes "Can we have On Error goto Err Hanlder Statements in Stored procedures like in VB.

Please give me an detailed example. I am having an stored procedure which gets executed daily at 11.30PM. so whenever it gets failed should be able to write a text file the error."

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-05-28 : 08:42:46

quote:

Adimulam Srinivasa Rao writes "Can we have On Error goto Err Hanlder Statements in Stored procedures like in VB.

Please give me an detailed example. I am having an stored procedure which gets executed daily at 11.30PM. so whenever it gets failed should be able to write a text file the error."

From BOL:

A. Use @@ERROR to detect a specific error
This example uses @@ERROR to check for a check constraint violation (error #547) in an UPDATE statement.

USE pubs
GO
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = "172-32-1176"

IF @@ERROR = 547
print "A check constraint violation occurred"

B. Use @@ERROR to conditionally exit a procedure
The IF...ELSE statements in this example test @@ERROR after an INSERT statement in a stored procedure. The value of the @@ERROR variable determines the return code sent to the calling program, indicating success or failure of the procedure.

USE pubs
GO

-- Create the procedure.
CREATE PROCEDURE add_author
@au_id varchar(11),@au_lname varchar(40),
@au_fname varchar(20),@phone char(12),
@address varchar(40) = NULL,@city varchar(20) = NULL,
@state char(2) = NULL,@zip char(5) = NULL,
@contract bit = NULL
AS

-- Execute the INSERT statement.
INSERT INTO authors
(au_id, au_lname, au_fname, phone, address,
city, state, zip, contract) values
(@au_id,@au_lname,@au_fname,@phone,@address,
@city,@state,@zip,@contract)

-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT "An error occurred loading the new author information"
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT "The new author information has been loaded"
RETURN(0)
END
GO

C. Use @@ERROR to check the success of several statements
This example depends on the successful operation of the INSERT and DELETE statements. Local variables are set to the value of @@ERROR after both statements and are used in a shared error-handling routine for the operation.

USE pubs
GO
DECLARE @del_error int, @ins_error int
-- Start a transaction.
BEGIN TRAN

-- Execute the DELETE statement.
DELETE authors
WHERE au_id = '409-56-7088'

-- Set a variable to the error value for
-- the DELETE statement.
SELECT @del_error = @@ERROR

-- Execute the INSERT statement.
INSERT authors
VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',
'6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
-- Set a variable to the error value for
-- the INSERT statement.
SELECT @ins_error = @@ERROR

-- Test the error values.
IF @del_error = 0 AND @ins_error = 0
BEGIN
-- Success. Commit the transaction.
PRINT "The author information has been replaced"
COMMIT TRAN
END
ELSE
BEGIN
-- An error occurred. Indicate which operation(s) failed
-- and roll back the transaction.
IF @del_error <> 0
PRINT "An error occurred during execution of the DELETE
statement."

IF @ins_error <> 0
PRINT "An error occurred during execution of the INSERT
statement."

ROLLBACK TRAN
END
GO

Expect the UnExpected

Lavos
Posting Yak Master

200 Posts

Posted - 2003-05-28 : 22:25:15

Just to make certain you know:

No matter what the DBA tells you, there is no structured error handling in T-SQL.

Oh, there's Error handling in T-SQL, but there's still no strcutured error handling in T-Sql.

Okay, enough references.

In addition to @@Error, you are going to find that some errors immediately cause the procedure to abort processing, so that there is NO error handling you can do in the code.

Since you are running a procedure in what appears to be a job, I'm thinking you are using SQLAgent? One of the options you can set on the job is what to do when a job fails, which can vary between nothing to calling the administrator's beeper. I would suggest reading the SQL Server documentation to see if there is an option to do exactly what you want as far as error tracking goes.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

So Long and Thanks for the Links.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources