T-SQL Throw Exception

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?

Solution 1:

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;

Solution 2:

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.

Solution 3:

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

Solution 4:

Put ; before THROW keyword and it will work.