Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - semicolon before THROW

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

like image 937
Lukasz Szozda Avatar asked Nov 14 '16 19:11

Lukasz Szozda


People also ask

Is semicolon necessary in SQL Server?

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.

Is it mandatory to enclose every SQL query with the semicolon at the end?

The semicolon (;) is used in SQL code as a statement terminator. For most SQL Server T-SQL statements it is not mandatory.

How do you insert a semicolon in SQL?

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.

What must follow the statement before the throw statement?

The statement before the THROW statement must be followed by the semicolon (;) statement terminator.


1 Answers

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.

like image 142
Martin Smith Avatar answered Sep 30 '22 01:09

Martin Smith