Raiserror syntax in 2008R2 Vs 2012

  • Hi all,

    I am trying to understand the syntax change for Raiserror from 2008 to 2012

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    CREATE procedure [dbo].[Spr_RaiseError]

    as

    declare @Rcount int

    BEGIN

    set @Rcount = 1

    if @Rcount > 0

    Begin

    Raiserror 50005 "Raise Custom Error Message"

    return -1

    End

    End

    GO

    When i complie and execute the above procedure it give me

    Msg 50005, Level 16, State 1, Procedure Spr_RaiseError, Line 11

    Raise Custom Error Message

    But i am not able to compile the same in 2012

    Msg 102, Level 15, State 1, Procedure Spr_RaiseError, Line 8

    Incorrect syntax near '50005'.

  • I have run into this before too. Note this thread on stackoverflow: RAISERROR issue since migration to SQL Server 2012

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • rxm119528 (4/9/2015)


    Hi all,

    I am trying to understand the syntax change for Raiserror from 2008 to 2012

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    CREATE procedure [dbo].[Spr_RaiseError]

    as

    declare @Rcount int

    BEGIN

    set @Rcount = 1

    if @Rcount > 0

    Begin

    Raiserror 50005 "Raise Custom Error Message"

    return -1

    End

    End

    GO

    When i complie and execute the above procedure it give me

    Msg 50005, Level 16, State 1, Procedure Spr_RaiseError, Line 11

    Raise Custom Error Message

    But i am not able to compile the same in 2012

    Msg 102, Level 15, State 1, Procedure Spr_RaiseError, Line 8

    Incorrect syntax near '50005'.

    There are no changed in the syntax from SQL Server 2008 but it changed between 2000 to 2005, comparing the syntax for the three versions shows the changes:

    I tried your code on couple of versions (2005/2008) with all settings to default and it did not parse! Issues were the missing parentheses, missing comma between the parameters, the order of the parameters and the double quotes.

    😎

    SQL Server 2000 and earlier

    RAISERROR ( { msg_id | msg_str } { , severity , state }

    [ , argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

    SQL Server 2005 to 2008 R2

    RAISERROR ( { msg_id | msg_str | @local_variable }

    { ,severity ,state }

    [ ,argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

    SQL Server 2012 and later

    RAISERROR ( { msg_id | msg_str | @local_variable }

    { ,severity ,state }

    [ ,argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

    Quick question, what is the compatibility settings for the 2008 server?

  • Eric,

    the compatibility mode is set to SqlServer2008(100).

    Thanks

    RM.

  • rxm119528 (4/9/2015)


    Eric,

    the compatibility mode is set to SqlServer2008(100).

    Thanks

    RM.

    Seen almost the same before, the difference being the text qualifiers and the missing parameter delimiter. Guess you will have to query the sys.all_sql_modules

    😎

    SELECT

    OBJECT_NAME(ASM.object_id) AS OBJ_NAME

    ,OBJECT_SCHEMA_NAME(ASM.object_id) AS OBJ_SCHEMA_NAME

    ,ASM.definition

    FROM sys.all_sql_modules ASM

    WHERE ASM.definition LIKE N'%RAISERROR%'

    AND ASM.object_id > 100;

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply