As we know ending each statement with semicolon is a good practice. Let's assume we have some old code which uses RAISERROR
to rethrow exceptions and we want to exchange it with THROW
.
From: THROW
:
The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
Using only ROLLBACK
:
BEGIN TRY
-- some code
SELECT 1;
END TRY
BEGIN CATCH
-- some code
ROLLBACK
THROW;
END CATCH
and we get Incorrect syntax near 'THROW'.
which is perfectly valid.
But using ROLLBACK TRANSACTION
works without semicolon:
BEGIN TRY
-- some code
SELECT 1;
END TRY
BEGIN CATCH
-- some code
ROLLBACK TRANSACTION
THROW;
END CATCH
LiveDemo
Finally using ROLLBACK TRANSACTION @variable
:
DECLARE @TransactionName NVARCHAR(32) = 'MyTransactionName';
BEGIN TRY
-- some code
SELECT 1;
END TRY
BEGIN CATCH
-- some code
ROLLBACK TRANSACTION @TransactionName
THROW;
END CATCH
and once again we get Incorrect syntax near 'THROW'.
.
Is there any particular reason why the second example works(backward compatiblity/...)?
EDIT:
There is great article written by Erland Sommarskog: Using ;THROW
Semicolon after SQL Statements? Some database systems require a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
The semicolon (;) is used in SQL code as a statement terminator. For most SQL Server T-SQL statements it is not mandatory.
Navigate to the SQL Complete menu and select Insert Semicolons. Right-click in the query window and click Insert Semicolons. Press Ctrl+B, then Ctrl+C.
The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
If you alter the code so it actually throws an error.
BEGIN TRY
-- some code
SELECT 1/0;
END TRY
BEGIN CATCH
-- some code
ROLLBACK TRANSACTION
THROW;
END CATCH
You see
Msg 3903, Level 16, State 1, Line 7 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
It is trying to roll back to a save point called THROW
.
This is valid syntax but fails at runtime in the above example as no transaction exists. If you are in an open transaction but have no such save point (as below)
BEGIN TRY
BEGIN TRAN
SELECT 1/0;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
THROW;
END CATCH
ROLLBACK
you see the following instead.
Cannot roll back THROW. No transaction or savepoint of that name was found.
This kind of ambiguity is presumably why the requirement for a preceding semi colon before throw
exists.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With