Knowledge Required:
- T-SQL
- SQL Server 2005
- Stored Procedure
Description:
In Stored Procedure (having lots of INSERTs, UPDATESs and DELETEs) we use Transaction (Begin Tran, Commit Tran, RollBack Tran). We usually begin with Begin Tran and on some error we do RollBack Tran and if NO error occurred we Commit Tran.
A better way to do this task is to use the Try...Catch in SQL Server 2005. For example:
Begin Try
BEGIN TRAN;
INSERT INTO ....
SELECT ...
INSERT INTO ...
DELETE ...
DELETE ...
INSERT ...
COMMIT TRAN;
End Try
Begin Catch
ROLLBACK TRAN;
DECLARE @ErrorMsg varchar(max);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
SET @ErrorMsg = ERROR_Message();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
RAISERROR(@ErrorMsg, @ErrorSeverity, @ErrorState);
End Catch
As you can see,
1) We start the Transaction in Try...Catch block
2) Next we move forward and try to do insert, update and delete
3) If any error occurred during these statements the Exception will be caught in the Begin Catch...End Catch block
4) Then in this block we have first Rolled Back the Transaction
5) Then again throw the exception so the caller should know that some error was occurred during processing
6) And if NO exception occured then finally before the End Try we have Committed the Transaction
