RAISERROR issue since migration to SQL Server 2012

Question

I am working on some issues with a website and database after a move of server. The database was previously SQL Server Express 2005 but is now running on SQL Server Express 2012.

The issues relate to the RAISERROR command and changes in 2012. I have looked at the documentation for the new syntax but am unsure on how I can pass both the error number and the message to the website.

Some examples of the RAISERROR commands in stored procedures and triggers are:

RAISERROR 50000 'Member with same Email address already exists.'

RAISERROR 44447 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tblBrand''.'

RAISERROR 44446 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tblFragranceHouse''.'

I have changed some to the new syntax but am unsure if I have done this correctly or not. I understand that if I just pass the error text it passes error number 50000. But I'm not sure what to do about the other error codes.

Any advice on how to translate these commands to 2012?

Answer 1

SQL 2012 does not support the undocumented version of Raiserror The supported syntax is

RAISERROR(@Message,Serverity,state); 

-- @Message could be message id , but it should exist in sysmessages, so if you want to send custom messages, I think you should add them sysmessages

http://msdn.microsoft.com/en-us/library/ms178592.aspx

Or the other option is to use THROW

http://technet.microsoft.com/en-us/library/ee677615.aspx