VistaDB 5
RAISERROR

Generates an error message with text or a number and throws an exception back to the hosting application if the script does not catch it. This behavior is slightly different than SQL Server.

It is a best practice to always put raiserror calls in a try catch block in the sql script. This will ensure the same behavior between VistaDB and SQL Server.

SQL Syntax

RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

SEVERITY, and STATE are ignored in the current VistaDB engine but are kept present in the RAISERROR call for SQL Server compatibility.

WITH is not supported.

Arguments are not supported as they must accept printf style macros.

Example

Raiserror Example
Copy Code
BEGIN TRY
   -- BUSINESS LOGIC
   -- IT DETECTS A BAD STATE
   RAISERROR( 5000, 1, 1)
END TRY
BEGIN CATCH
   IF @@ERROR = 5000 PRINT 'Got Error 5000'
   ELSE PRINT @@Error
END CATCH

Differences from SQL Server

SQL Server has some very strange behavior for this function. If your error level is above a certain amount it is treated differently, and if you are within a try catch block (at some level, not just your local function) then the behavior is totally different.

You need to be aware that the error numbers in SQL Server has significant meanings, and you are not allowed to just choose your own number. They have to be registered in the system. Obviously we will not be supporting that type of system. You can throw an error number, or a message. We just pass them up the stack with the error.

Example Title
Copy Code
BEGIN TRY
   IF (1=1)
   BEGIN
      PRINT 'BEFORE ERROR 1';
         RAISERROR( 'MY ERROR', 16, 1);
         PRINT 'AFTER ERROR 1';
   END
  
   PRINT 'NEVER GOT HERE ';
END TRY
BEGIN CATCH
   PRINT '---CAUGHT ERROR---';
   PRINT @@Error
END CATCH

This code in VistaDB will show the BEFORE ERROR 1, and then --CAUGHT ERROR--.
On SQL Server, if you are in a try catch block, it does the same thing.

Now just highlight the part within the try area:

Example Title
Copy Code
IF (1=1)
BEGIN
   PRINT 'BEFORE ERROR 1';
      RAISERROR( 'MY ERROR', 16, 1);
      PRINT 'AFTER ERROR 1';
END

PRINT 'NEVER GOT HERE ';


In SQL Server you see:

BEFORE ERROR 1
MY ERROR
Msg 50000, Level 1, State 1
AFTER ERROR 1
NEVER GOT HERE

Huh? The after error is called? And you actually get nothing other than a warning message on the console 'MY ERROR' in red. That is not correct behavior in my opinion.

VistaDB will raise the error up to your application (like a throw in .Net). Your application will then have to handle the error. It is not an informational message to be displayed, it is an error. Otherwise why did you call raiserror? You could have called print if you want a message.


Example in Data Builder

RAISERROR( 'Test', 1, 1);


RAISERROR( 5000, 1, 1);

See Also