SQL 2012 issue with RAISERROR syntax

[dhookom]

I am tasked with running a SQL script that creates tables, views, stored procedures, etc for a corporate application. We are attempting our first installation using SQL 2012 and have run into an issue with RAISERROR. There are 26 instances of RAISERROR in the script similar to the following:

RAISERROR 44444 @ERRMSG;
RAISERROR(N'Cannot insert/update ABCUSERS because there is already a Windows Account ID (WINID) with the same value', 16, 1);
RAISERROR @ERRNUM @ERRMSG;
RAISERROR 44444 N'An error occured while inserting into table ERRDST';

I am in dire need of some direction regarding updating the script. Do I need to use sys.sp_addmessage or is there another option?

Duane
Hook'D on Access
MS Access MVP

 

[gmmastros]

I encourage you to take a look at the new (to SQL2012) statement called THROW.

http://msdn.microsoft.com/en-us/library/ee677615(v=sql.110).aspx

The THROW syntax is similar to the RAISERROR syntax in earlier versions of SQL.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dhookom]

Thanks George. It looks like THROW needs to be used with TRY and CATCH? If I don't use TRY/CATCH, it seems "the session is ended" which doesn't provide the same functionality as the RAISERROR in the 2008 syntax. I also assume I would need to change the "44444" to a number bewteen 50000 and 2147483647.

I was hoping to use a quick find and replace that would get me on my way without making major modifications. The entire script is about 14,000 lines.

Duane
Hook'D on Access
MS Access MVP