T-SQL Throw Exception
Solution 1
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 2
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 3
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 4
Put ;
before THROW
keyword and it will work.
Solution 5
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"
![user3021830](https://i.stack.imgur.com/mMOGr.png?s=256&g=1)
user3021830
Updated on July 05, 2022Comments
-
user3021830 almost 2 years
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?
-
Levi Botelho almost 9 yearsStatements that are not terminated with a semicolon are now deprecated, so even when not throwing, this is a good habit to get into.
-
it2051229 over 8 yearsThat was frustrating. I misspelled it also to RAISEERROR. Spent almot an hour finding solution.
-
jkyadav almost 7 yearsGood info, Roger
-
Storm Muller over 6 yearsPlease don't put semi colons before a statement. Put it after a statement on the previous line.
-
René Nyffenegger over 5 yearsPutting semicolons after
BEGIN
just looks weird, imho. -
Alexandre N. over 5 yearsSemicolon just finish last instruction if you not. This ensures that the error doesn't occur.
-
David over 5 yearsMake sure the statement preceding THROW ends with semicolon ";". For example you could have a "PRINT 'Before Throw';" statement.
-
WillC over 5 yearsI think that it was the developer that named it RAISERROR was the one that misspelled it...
-
LarryBud over 4 yearsThis 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.
-
d219 almost 4 yearsThis also works if other keywords are having a problem e.g I had an
ENABLE TRIGGER
command after anEND CATCH
- adding the semi-colon resolved it.