Once upon a time, back in the 60s, some MIT nerds took it upon themselves to “waste” valuable research computer time to compute the ideal route to ride the entire New York subway system. This is a pretty interesting look at how to coordinate a project to achieve a monumental feat purely for the point of achieving it. Sort of like Alex Roy’s 31 hour drive across the US.
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.
BEGIN TRANSACTION
-- DROP TEMP TABLES
IF OBJECT_ID('tempdb..#my_temp_table') IS NOT NULL
DROP TABLE #my_temp_table;
BEGIN TRY
-- LOGIC GOES HERE
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
-- Error handling to ensure that transactions are appropriately rolled back on error
