Try & Catch In Stored Procedure

Try Catch block can be used into SQL Server 2005 too inside the Stored Procedure, this facilitate the user to make the code easily readable and raise appropriate error with in-depth details.

BEGIN TRY 
   BEGIN TRANSACTION    -- Start the transaction 
-- DO your worklike INSERT, UPDATE, DELETE 
 
-- If we reach here, success! 
COMMIT -- commit the transaction 
END TRY 
BEGIN CATCH 
  -- Whoops, there was an error 
  IF @@TRANCOUNT > 0 
 ROLLBACK 
END CATCH

For more details, you can visit http://www.4guysfromrolla.com/webtech/041906-1.shtml