T-SQL Throw Exception
Sql ServerTsqlThrowSql Server Problem Overview
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?
Sql Server Solutions
Solution 1 - Sql Server
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 - Sql Server
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 - Sql Server
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.
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 - Sql Server
Put ;
before THROW
keyword and it will work.
Solution 5 - Sql Server
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"
Solution 6 - Sql Server
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.
Solution 7 - Sql Server
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.