A collection of common Azure SQL Server template for advanced SQL Scripts. Starting out small.
TRY CATCH with transaction rollback
Error handling in SQL servers can be verbose and a bit messy. This is about the cleanest approach which works in most situations.
--setup temp table for testing
CREATE TABLE #TEMP ( ID TINYINT NOT NULL );
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO #TEMP( ID ) VALUES ( 2 )
INSERT INTO #TEMP( ID ) VALUES ( 256 ) --should cause error - max is 255
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW; --bubble up / output to console. cleaner than RAISEERROR
END CATCH
--teardown the temp table
DROP TABLE #TEMP;