Sunday, November 20, 2011

Error Handling store procedure

CREATE PROCEDURE usp_Example_ErrorHandler

/* Example stored procedure to illustrate error handling
* Example:
DECLARE @RC int
EXEC @RC = usp_Example_ErrorHandler
PRINT 'Return value = ' + CONVERT(varchar(10), @RC)
****************************************************************/
AS

DECLARE @myERROR int -- Local @@ERROR
, @myRowCount int -- Local @@ROWCOUNT

SET NOCOUNT ON

BEGIN TRAN
INSERT INTO Authors (au_id, au_fname, au_lname, contract)
VALUES ('222-22-2222'
, 'Andrew'
, 'Novick'
, 1
)

SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
IF @myERROR != 0 GOTO HANDLE_ERROR

INSERT INTO titles(title_id, title, type, price,notes, pubdate)
VALUES('WW0790'
, 'Transact-SQL User-Defined Functions'
, 'popular_comp', 49.95
, 'Great book.', '2003-11-04')
SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
IF @myERROR != 0 GOTO HANDLE_ERROR

INSERT INTO titleauthor (au_id, title_id)
VALUES('222-22-2222', 'WW0790')
SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
IF @myERROR != 0 GOTO HANDLE_ERROR

COMMIT TRAN -- No Errors, so go ahead

RETURN 0

HANDLE_ERROR:
ROLLBACK TRAN
RETURN @myERROR

GO

No comments:

Post a Comment