Try & Catch In Stored Procedure

Question

Hi Friends.. I want to know How to use of try & Catch block in stored procedure ? Please explain with example ? Thanks...

Hemant Kumar · Answer

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