Raiserror syntax in 2008R2 Vs 2012

Question

Raiserror syntax in 2008R2 Vs 2012

rxm119528

SSCrazy

Points: 2960
More actions
April 9, 2015 at 10:53 am

#299747

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

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

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

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

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

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?

rxm119528 SSCrazy Points: 2960 More actions · Answer 3

Eric,

the compatibility mode is set to SqlServer2008(100).

Thanks

RM.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

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;