facility9

Over time, I’ve had the need for more and more robust error handling in SQL. Initially I accomplished this with transactions, but eventually I needed something more. Finally, I came up with a template that I have started to use every time I start writing a new SQL file.

1. BEGIN TRANSACTION
2.  
3. -- DROP TEMP TABLES
4. IF OBJECT_ID('tempdb..#my_temp_table') IS NOT NULL
5. DROP TABLE #my_temp_table;
6.  
7. BEGIN TRY
8. -- LOGIC GOES HERE
9. END TRY
10.  
11. BEGIN CATCH
12. SELECT
13. ERROR_NUMBER() AS ErrorNumber,
14. ERROR_SEVERITY() AS ErrorSeverity,
15. ERROR_STATE() AS ErrorState,
16. ERROR_PROCEDURE() AS ErrorProcedure,
17. ERROR_LINE() AS ErrorLine,
18. ERROR_MESSAGE() AS ErrorMessage;
19. END CATCH
20.  
21. -- Error handling to ensure that transactions are appropriately rolled back on error
22. -- and not left hanging
23. SELECT @error_code = @@ERROR
24. IF (@error_code <> 0) GOTO ERR_HANDLER
25.  
26. -- CHANGE THIS TO COMMIT ON DEPLOYMENT
27. ROLLBACK TRANSACTION
28. GOTO THE_END
29.  
30. ERR_HANDLER:
31. IF (@error_code <> 0) BEGIN
32. SELECT 'Unexpected errors occurred!'
33. ROLLBACK TRANSACTION
34. END
35.  
36. THE_END:
37. Download this code: sampleerrors.sql