Incorrect Syntax Near Throw
Question
Monday, August 12, 2013 3:48 PM | 1 vote
I have what looks to be a very simple Stored Procedure as follows:
CREATE PROCEDURE SOE_OFMUpdateStatus_v1
@sOrderNumber numeric(7,0),
@sOrderType char,
@sSupCode char,
@sOrderStatus varchar(15),
@iPurgeDateOffset int,
@cUserID varchar(10)
WITH EXECUTE AS CALLER
AS
BEGIN
UPDATE SVCORDER
SET STATUS = @sOrderStatus,
APPDT = CONVERT(datetime2, GETDATE()),
PURGE_DT = DATEADD(d, @iPurgeDateOffset, DUEDT),
CUA_USERID = @cUserID,
LOCK_IND = @cUserID
WHERE ORDERTYPE = @sOrderType
AND ORDR_NBR = @sOrderNumber
AND SUP_CODE = @sSupCode;
IF 1 = @@ROWCOUNT
RETURN 0; -- SUCCESS
THROW -1, 'OFMUpdateStatus did not match exactly one record.', 1
-- RETURN -1; -- FAIL
END;
When I execute the SQL Script in SQL Server Management Studio, everything is just fine. I have included this SP in a VS 2012 Database Project, and when I build, I get the error 'Incorrect Syntax near Throw'. The Database project is set to SQL 2012. I did try putting a ';' in front of the THROW as has been suggested on other threads. That does not fix the issue.
Is there a fix for this issue, or is this a case where we will have to abandon use of VS 2012 Database integration?
John
All replies (12)
Wednesday, September 4, 2013 8:39 PM âś…Answered | 2 votes
This does look like a bug. I'll create a bug for the appropriate team. If we get more requests for this fix it'll help us prioritize.
Monday, August 12, 2013 4:32 PM
Please try this;
CREATE PROCEDURE SOE_OFMUpdateStatus_v1
@sOrderNumber NUMERIC(7, 0) ,
@sOrderType CHAR ,
@sSupCode CHAR ,
@sOrderStatus VARCHAR(15) ,
@iPurgeDateOffset INT ,
@cUserID VARCHAR(10)
WITH EXECUTE AS CALLER
AS
BEGIN TRY
UPDATE SVCORDER
SET STATUS = @sOrderStatus ,
APPDT = CONVERT(DATETIME2, GETDATE()) ,
PURGE_DT = DATEADD(d, @iPurgeDateOffset, DUEDT) ,
CUA_USERID = @cUserID ,
LOCK_IND = @cUserID
WHERE ORDERTYPE = @sOrderType
AND ORDR_NBR = @sOrderNumber
AND SUP_CODE = @sSupCode;
END TRY
BEGIN CATCH
THROW 50001, 'OFMUpdateStatus did not match exactly one record.', 1;
END CATCH;
If This post is helpful post, please vote it. If This post is the answer to your question, please Propose it as answer. Thanks so much for your feedback.
Monday, August 12, 2013 4:46 PM
The suggestion you make is NOT functionally the same. The original code checks for the number of rows updated by the Update statement and throws an exception if a condition is not met.
The code you suggest will only throw an error if the Catch block is executed, which will not occur in this case.
Your suggestion will not provide the necessary application functionality.
Are you suggesting that THROW is only valid in a CATCH block? If that is the case, then THROW cannot be used at all, at least not in this case [where there is an application business rule that results in the creation of an Exception].
John
Wednesday, August 14, 2013 6:58 PM
What do we need to do on this forum to get someone from MSFT to comment on the issue?
I received a reply to my question, but it was not functionally equivalent, and therefore, was not useful.
This just looks like a bug in the VS 2012 support for SQL Server 2012 syntax.
John
Wednesday, August 14, 2013 8:56 PM | 4 votes
The keyword "THROW" is a SQL 2012 function. I suspect your target database of your project is set to SQL 2008.
Thursday, August 15, 2013 1:13 PM
No. It is not. I mentioned this fact in the initial post on this thread. See the line 'The Database project is set to SQL 2012' in the initial thread.
As I said, this appears to be a bug in the VS 2012 support for SQL 2012.
John
Thursday, August 15, 2013 1:39 PM
That does appear to be a bug in the parser. It does not allow THROW except inside TRY/CATCH. This is not techncially correct.
CREATE PROCEDURE Proc1
AS
BEGIN
THROW 51000, 'The record does not exist.', 1; -- Says Syntax Error
BEGIN TRY
PRINT 'Test';
THROW 51000, 'The record does not exist.', 1;
END TRY
BEGIN CATCH
THROW 51000, 'The record does not exist.', 1;
END CATCH
END
Sunday, June 22, 2014 2:07 PM | 1 vote
Hi everyone. I like to find out if there is already a fix to this problem. I am experiencing the same issue with THROW and using SQL Server 2012 preparing for the 70-461.
Thanks
Tuesday, August 4, 2015 5:09 PM
Hello,
I just encountered this issue on VS 2015 (Enterprise Version 10.0.23107.0). The following trigger, the SQL server management studio does not complain and SQL Server 2014 accepts the trigger without error, but it VS 2015 does not like it.
CREATE TRIGGER web.Account_tr_Delete ON web.Account
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
THROW -1, 'Deletion of web.Account is not Permitted', -1;
END ;
It reports that
Error: SQL46010: Incorrect syntax near THROW.
What would be the workaround for this? Should I keep using RAISERROR for now?
Sunday, July 3, 2016 11:53 AM | 1 vote
Have you tried to encapsulate the throw statement within a BEGIN TRY ... END TRY-Block (as already probosed by Saeid Hasani)?
I made a slightly change to his code to cover your requirements.
CREATE PROCEDURE SOE_OFMUpdateStatus_v1
@sOrderNumber numeric(7,0),
@sOrderType char,
@sSupCode char,
@sOrderStatus varchar(15),
@iPurgeDateOffset
int,
@cUserID varchar(10)
WITH EXECUTE AS CALLER
AS
BEGIN TRY
UPDATE SVCORDER
SET STATUS = @sOrderStatus,
APPDT = CONVERT(datetime2, GETDATE()),
PURGE_DT = DATEADD(d, @iPurgeDateOffset, DUEDT),
CUA_USERID = @cUserID,
LOCK_IND = @cUserID
WHERE ORDERTYPE = @sOrderType
AND ORDR_NBR = @sOrderNumber
AND SUP_CODE = @sSupCode;
IF 1 = @@ROWCOUNT
RETURN 0; -- SUCCESS
ELSE
THROW -1, 'OFMUpdateStatus did not match exactly one record.', 1
-- RETURN -1; -- FAIL
END TRY
BEGIN CATCH
THROW;
END CATCH;
I had a similar issue and solved it by using Begin Try... END try and Begin Catch ... End Catch
First I copied my SP to VS it showed an error even I used BEGIN TRY. The issue for this was that between Begin Try and my first THROW statement was another BEGIN. I commented the BEGIN and uncommented it again and everything works fine. :-)
Regards, Gregor
Wednesday, December 20, 2017 3:07 PM
CREATE PROCEDURE .....
AS
BEGIN TRY
SET NOCOUNT ON
;THROW 50001, 'Impossibruuu!.', 1;
END TRY
BEGIN CATCH
THROW; -- rethrows internal exception
END CATCH
GO
Cheers!
Thursday, June 7, 2018 8:19 PM
I had the following without the semicolons at the end and it was giving me issues.
Added the semicolons, no more issues
IF @Person1_lookupPerson1TypeUID IS NULL
BEGIN
DECLARE @mesage VARCHAR(2048);
SET @mesage = N'The provided Person1 Type ['+@Person1_Person1Type+'] was invalid.';
THROW 60000, @mesage, 1;
END;