Error Handling SP with Transaction

Let´s see best practices implemented on creating Stored procedures. So when we create the procedure we must implement some kind of error handling to control. So let´s see what is the syntax:

Create Procedure Nametheprocedure
-- Parameters input or output
As
BEGIN TRY
Begin Transaction
-- Commands
commit transaction -- If it runs without error commit the transaction

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER(), ERROR_MESSAGE()

Rollback
END CATCH

GO

So these is the syntax and bellow is an example



USE AdventrureWorks
go
CREATE PROCEDURE Production.AddReview
@ProductID int,
@ReviewerName nvarchar(50),
@EmailAddress nvarchar(50),
@Rating int,
@Comments nvarchar(3850)
as


BEGIN TRY
Begin transaction;
INSERT Production.ProductReview (ProductID, ReviewerName, EmailAddress, Rating, Comments)
VALUES (@ProductID, @ReviewerName, @EmailAddress, @Rating, @Comments)
commit transaction;
END TRY

BEGIN CATCH

SELECT ERROR_NUMBER(), ERROR_MESSAGE()
rollback transaction;
END CATCH

GO

-- Test stored procedure with error handling
EXECUTE Production.AddReview 715, 'Doug', 'doug@siteexample.com', 10, 'Great product!'
GO

See you Soon :)

Related Posts:

• Stored procedure to copy files

• SQL date manipulation

• Database Triggers

• Create SP with Cursor