1

I have moved a database form a old legacy MSSQL server 2000 (SQL Server 8) to SQL Server 11 and im receiving some errors in several triggers.

They are formed like this.

RAISERROR 20001 @msg

and

RAISERROR 44444 'Field ''comp_v1'' cannot contain a null value.'

so after some reading i see that this is a known change, but i cant find any good known solutions.

At first i thought to just replace them to something like this

RAISERROR ('Field ''comp_v1'' cannot contain a null value.',16,-1)

The issue here is, well i have no real idea on what effect this has, and if there is any good straightforward conversion from the old line to a new line.

Im assuming that there is a reason for the codes 44444 and 20001 etc.

Thomas Andreè Wang
  • 3,379
  • 6
  • 37
  • 53

1 Answers1

1

I would advise you to use THROW keyword to throw the error. You can check this for reasons:

With THROW the benefit is: it is not mandatory to pass any parameter to raise an exception. Just using the THROW; statement will get the error details and raise it

whereas with RAISERROR

With RAISERROR developers had to use different ERROR_xxxx() system functions to get the error details to pass through the RAISERROR() statement, like:

  • ERROR_NUMBER()
  • ERROR_MESSAGE()
  • ERROR_SEVERITY()
  • ERROR_STATE()
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • So the solution is to just `THROW`? There is no magic conversion from the old to the new? Will the system perform with `THROW` as did with `RAISERROR` ? – Thomas Andreè Wang Feb 08 '16 at 13:38
  • 1
    @ThomasAndreèLian:- Yes you can use THROW. It will do the same job as RAISERROR and with this you don't have to care for ERROR_MESSAGE and all other stuff :) – Rahul Tripathi Feb 08 '16 at 13:40
  • Is there a way to deliver a custom message with THROW? – Thomas Andreè Wang Feb 08 '16 at 13:47
  • @ThomasAndreèLian:- [Yes you can parameterize the THROW stament.](https://msdn.microsoft.com/en-IN/library/ee677615.aspx) – Rahul Tripathi Feb 08 '16 at 13:51
  • @ThomasAndreèLian:- [But if you parameterize the THROW statement as above it will not show the actual position of exception occurrence, and the behavior will be same as RAISERROR(). As with RAISERROR() you've to provide mandatory params, so there is no way to get the actual position of Line where the error occurred.](http://blogs.msdn.com/b/manub22/archive/2013/12/31/new-throw-statement-in-sql-server-2012-vs-raiserror.aspx) – Rahul Tripathi Feb 08 '16 at 13:51
  • Quick question, does Raise and Throw do the same in SQL as in for an example C#? does it exit the script? or can a `ROLLBACK TRANSACTION` come after? – Thomas Andreè Wang Feb 08 '16 at 14:08
  • 1
    @ThomasAndreèLian:- Once you do a RAISERROR or THROW then you usually you call a RETURN. You can also check this thread: [Why does Sql Server keep executing after raiserror when xact_abort is on?](http://stackoverflow.com/questions/76346/why-does-sql-server-keep-executing-after-raiserror-when-xact-abort-is-on) – Rahul Tripathi Feb 08 '16 at 14:11
  • Just figured out that there are MAJOR differences between `THROW` and `RAISERROR`. Using THROW as a substitute is a very poor solution at best as they don't perform the same, for my problem using it blindly would cause a lot of money going down the drain if it went live http://sqlhints.com/2013/06/30/differences-between-raiserror-and-throw-in-sql-server/ – Thomas Andreè Wang Feb 08 '16 at 14:41