locked
Incorrect Syntax Near Throw RRS feed

  • Question

  • 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

    Monday, August 12, 2013 3:48 PM

Answers

  • 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.
    • Marked as answer by Janet Yeilding Thursday, September 12, 2013 4:58 PM
    Wednesday, September 4, 2013 8:39 PM

All replies

  • 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.

    • Proposed as answer by Saeid Hasani Friday, September 6, 2013 3:03 PM
    Monday, August 12, 2013 4:32 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

    Monday, August 12, 2013 4:46 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 6:58 PM
  • The keyword "THROW" is a SQL 2012 function.  I suspect your target database of your project is set to SQL 2008.


    Wednesday, August 14, 2013 8:56 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:13 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

    • Proposed as answer by psirr-MSFT Wednesday, September 4, 2013 8:40 PM
    Thursday, August 15, 2013 1:39 PM
  • 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.
    • Marked as answer by Janet Yeilding Thursday, September 12, 2013 4:58 PM
    Wednesday, September 4, 2013 8:39 PM
  • 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
    Sunday, June 22, 2014 2:07 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?


    Tuesday, August 4, 2015 5:09 PM
  • 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


    • Edited by Gregor Prohaska Sunday, July 3, 2016 11:57 AM reference to answer of Saeid Hasani
    • Proposed as answer by Gregor Prohaska Tuesday, July 5, 2016 11:05 AM
    Sunday, July 3, 2016 11:53 AM
  • CREATE PROCEDURE .....
    AS
    BEGIN TRY
    	SET NOCOUNT ON
    	;THROW 50001, 'Impossibruuu!.', 1;
    END TRY
    BEGIN CATCH
    	THROW; -- rethrows internal exception
    END CATCH
    GO
    Cheers!
    Wednesday, December 20, 2017 3:07 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;

    Thursday, June 7, 2018 8:19 PM