T-SQL Throw Exception

Question

I am facing the famous 'Incorrect syntax' while using a THROW statement in a T-SQL stored procedure. I have Googled it and checked the questions on StackOverflow but the solutions proposed (and strangely, accepted) do not work for me.

I am modifying a stored procedure as follows:

ALTER PROCEDURE [dbo].[CONVERT_Q_TO_O]
    @Q_ID int = NULL,
    @IDENTITY INT = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EXISTING_RECORD_COUNT [int];

    SELECT
        @EXISTING_RECORD_COUNT = COUNT (*)
    FROM
        [dbo].[O]
    WHERE
        [Q_ID] = @Q_ID

    IF @EXISTING_RECORD_COUNT = 0
    BEGIN
        -- DO SOME STUFF HERE

        -- RETURN NEW ID
        SELECT @IDENTITY = SCOPE_IDENTITY()
    END
    ELSE
    BEGIN
         THROW 99001, 'O associated with the given Q Id already exists', 1;
    END
END
GO

When I code this T-SQL I get an error saying

Incorrect statement near 'THROW'. Expecting CONVERSATION, DIALOG, DISTRIBUTED, or TRANSACTION

All solutions suggest to put a semi-colon either before 'THROW' or after 'ELSE BEGIN' statements. When I modify the T-SQL I simply get the "Incorrect statement near 'THROW'" error and can't seem to find a solution.

Any suggestions?

Chances are you're using a version that doesn't support THROW — Allan S. Hansen, Oct 15 '14 at 07:51
`THROW` is a **new feature** in SQL Server **2012**, so if you're working against anything older than 2012, you can't use that feature - check your `SELECT @@VERSION` output for what database engine version you're using! — marc_s, Oct 15 '14 at 07:53
Silly me. Runs on a version prior to 2012. I should better use RAISEERROR then. Thank you very much for you. Regards. — user3021830, Oct 15 '14 at 07:54
For the same error in SQL Server 2012+ see [Incorrect syntax near 'THROW'](http://stackoverflow.com/q/35003153) — Michael Freidgeim, May 27 '16 at 08:06
Possible duplicate of [I am unable to use THROW SQL Server 2008 R2](http://stackoverflow.com/questions/16670985/i-am-unable-to-use-throw-sql-server-2008-r2) — Michael Freidgeim, May 27 '16 at 08:07
Possible duplicate of [Incorrect syntax near 'THROW'](https://stackoverflow.com/questions/35003153/incorrect-syntax-near-throw) — Michael Freidgeim, Sep 26 '17 at 06:37

score 95 · Answer 1 · answered Mar 11 '15 at 15:58

95

This continues to occur in SQL Server 2014.

I have found that putting the semi-colon at the end of BEGIN helps.

This approach has the error

IF 'A'='A'
BEGIN
   THROW 51000, 'ERROR', 1;
END;

And this approach does not have the error

IF 'A'='A'
BEGIN;
  THROW 51000, 'ERROR', 1;
END;

answered Mar 11 '15 at 15:58

Roger Layton

1,573
2
13
12

20

Statements that are not terminated with a semicolon are now [deprecated](https://msdn.microsoft.com/en-us/library/ms143729(v=sql.120).aspx), so even when not throwing, this is a good habit to get into. – Levi Botelho Jul 12 '15 at 08:16
2

Good info, Roger – jkyadav Sep 18 '17 at 07:24
Make sure the statement preceding THROW ends with semicolon ";". For example you could have a "PRINT 'Before Throw';" statement. – David Feb 07 '19 at 18:42
10

This has to be one of the dumbest things I've seen with SQL. And semicolons will NEVER be required, billions of lines of code aren't going to be updated. That ship has sailed. – LarryBud Feb 26 '20 at 19:46
BRUH and my code was so pretty – zbarnz Dec 07 '22 at 22:39

score 43 · Answer 2 · answered Apr 11 '16 at 20:10

43

To solve your problem,

Incorrect statement near 'THROW'. Expecting CONVERSATION, DIALOG, DISTRIBUTED, or TRANSACTION

put semi-colon before your throw statement:

BEGIN
    ;THROW 99001, 'O associated with the given Q Id already exists', 1;
END

And about the

"Incorrect statement near 'THROW'".

Try to use this in case you're using a older version than SQL 2012:

RAISERROR('O associated with the given Q Id already exists',16,1);

Because THROW is a new feature of SQL 2012.

answered Apr 11 '16 at 20:10

Alexandre N.

2,594
2
28
32

13

Please don't put semi colons before a statement. Put it after a statement on the previous line. – Storm Muller Mar 19 '18 at 09:06
12

Putting semicolons after `BEGIN` just looks weird, imho. – René Nyffenegger Nov 12 '18 at 06:47
Semicolon just finish last instruction if you not. This ensures that the error doesn't occur. – Alexandre N. Nov 12 '18 at 12:33

SubqueryCrunch · Accepted Answer · 2021-06-30T07:07:30.277

For SQL Server 2012 or later:

;THROW 60000, 'your message here', 1

If you wish to pass a variable to your message use this:

DECLARE
    @Errors INT = 2,
    @ErrMsg NVARCHAR(500)

SET @ErrMsg = 'You have '+CAST(@Errors AS NVARCHAR) + ' errors!'
;THROW 60000, @ErrMsg, 1

Note that THROW blocks further code execution unlike RAISERROR.

THROW documentation

Legacy option:

RAISERROR('your message here', 16, 1)

If you wish to pass a variable to your message use this:

DECLARE
    @Errors INT = 2,
    @ErrMsg NVARCHAR(500)

SET @ErrMsg = 'You have '+CAST(@Errors AS NVARCHAR) + ' errors!'
RAISERROR(@ErrMsg, 16, 1)

To check sql server version: SELECT @@VERSION

score 12 · Answer 4 · edited Aug 21 '20 at 13:38

12

Put ; before THROW keyword and it will work.

edited Aug 21 '20 at 13:38

d219

2,707
5
31
36

answered Feb 23 '16 at 08:53

user1945580

121
1
3

This also works if other keywords are having a problem e.g I had an `ENABLE TRIGGER` command after an `END CATCH` - adding the semi-colon resolved it. – d219 Aug 21 '20 at 13:40

score 9 · Answer 5 · answered Nov 30 '15 at 14:59

9

This error can also occur if you incorrectly code this:

RAISEERROR('your message here',16,1)

I stared at that for four hours, putting semicolons all over the place, before I realized I'd misspelled "RAISERROR"

answered Nov 30 '15 at 14:59

djhill8262

235
3
5

3

That was frustrating. I misspelled it also to RAISEERROR. Spent almot an hour finding solution. – it2051229 Feb 14 '16 at 23:30
5

I think that it was the developer that named it RAISERROR was the one that misspelled it... – WillC Feb 13 '19 at 21:59

Pablo Corso · Answer 6 · 2017-02-08T18:56:22.657

As pointed out through many answers, the THROW statement was introduced in SQL Server 2012. So if you are using this version of SQL Server or later, it is recommended to use THROW, else use RAISERROR.

Also, the statement before the THROW statement must be followed by the semicolon (;) statement terminator. That's why you must include a semicolon before the throw.

Look at this article about the Differences Between RAISERROR and THROW in Sql Server

I would also like to encourage you to read the documentation from MSDN THROW (Transact-SQL) which explains these matters at the Remarks section.

score 0 · Answer 7 · answered Jun 08 '21 at 01:07

Thanks for posting this question. While the IDE gives the syntax error, I found it compiles and runs fine (note, I'm using SQL Server 2106).

I also found that if there is only the need for a single statement, I can just use

ELSE 
    THROW 99001, 'O associated with the given Q Id already exists', 1;

This doesn't present a syntax error message.

T-SQL Throw Exception

7 Answers7

Linked