VistaDB 6
VistaDB / Developer's Guide / SQL Reference / Control of Flow Statements / RAISERROR
In This Topic
    RAISERROR
    In This Topic

    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