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