Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Error handling in sql server 2005, and later versions - Part 56

Suggested SQL Server Videos
Part 18 - Stored Procedures
Part 55 - Error handling in SQL Server 2000

In Part 55, of this video series we have seen Handling errors in SQL Server using @@Error system function. In this session we will see, how to achieve the same using Try/Catch blocks.

     { Any set of SQL statements }
     [ Optional: Any set of SQL statements ]
[Optional: Any other SQL Statements]

Any set of SQL statements, that can possibly throw an exception are wrapped between BEGIN TRY and END TRY blocks. If there is an exception in the TRY block, the control immediately, jumps to the CATCH block. If there is no exception, CATCH block will be skipped, and the statements, after the CATCH block are executed.

Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using RAISERROR() function.

1. In procedure spSellProduct, Begin Transaction and Commit Transaction statements are wrapped between Begin Try and End Try block. If there are no errors in the code that is enclosed in the TRY block, then COMMIT TRANSACTION gets executed and the changes are made permanent. On the other hand, if there is an error, then the control immediately jumps to the CATCH block. In the CATCH block, we are rolling the transaction back. So, it's much easier to handle errors with Try/Catch construct than with @@Error system function.

2. Also notice that, in the scope of the CATCH block, there are several system functions, that are used to retrieve more information about the error that occurred  These functions return NULL if they are executed outside the scope of the CATCH block.

3. TRY/CATCH cannot be used in a user-defined functions.

Create Procedure spSellProduct
@ProductId int,
@QuantityToSell int
-- Check the stock available, for the product we want to sell
Declare @StockAvailable int
Select @StockAvailable = QtyAvailable 
from tblProduct where ProductId = @ProductId

-- Throw an error to the calling application, if enough stock is not available
if(@StockAvailable < @QuantityToSell)
Raiserror('Not enough stock available',16,1)
-- If enough stock available
  Begin Try
   Begin Transaction
       -- First reduce the quantity available
Update tblProduct set QtyAvailable = (QtyAvailable - @QuantityToSell)
where ProductId = @ProductId

Declare @MaxProductSalesId int
-- Calculate MAX ProductSalesId
Select @MaxProductSalesId = Case When 
MAX(ProductSalesId) IS NULL 
Then 0 else MAX(ProductSalesId) end 
from tblProductSales
--Increment @MaxProductSalesId by 1, so we don't get a primary key violation
Set @MaxProductSalesId = @MaxProductSalesId + 1
Insert into tblProductSales values(@MaxProductSalesId, @ProductId, @QuantityToSell)
   Commit Transaction
  End Try
  Begin Catch
Rollback Transaction
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_STATE() as ErrorState,
ERROR_SEVERITY() as ErrorSeverity,
ERROR_LINE() as ErrorLine
  End Catch

1 comment:

  1. thanks my Dear So much for your great job , but can you help me re to sql server Part 56 if i want to make transaction for many items in the same time and if any items there is not enough amount rollback all the transaction
    appreciated your reply


If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.