Syntax Analysis and Error Handling Mechanism of RAISERROR Function in SQL Server

Overview of RAISERROR Function

In SQL Server database development, the RAISERROR function is a crucial tool for handling errors and exceptional situations. This function is used to generate custom error messages and initiate error processing for the session. Unlike simple PRINT statements, RAISERROR provides richer error information and control flow capabilities.

Basic Syntax Structure

The standard syntax format of the RAISERROR function is as follows:

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

Where the msg_str parameter specifies the error message text, severity defines the error severity level, and state represents the error state identifier. These two parameters are essential components for the proper execution of the RAISERROR function.

Detailed Explanation of Severity Levels

Severity level is one of the core parameters of the RAISERROR function, with the following value ranges and meanings:

• Levels 0-10: Informational messages that do not raise exceptions, execution continues
• Levels 11-19: Error levels that trigger CATCH block execution, transactions can be rolled back
• Levels 20-25: Fatal errors that terminate database connections and log errors

The level 16 used in the trigger example belongs to the error level. When the condition @sales > 1000 is met, it throws an exception and executes the ROLLBACK operation.

Role of State Parameter

The state parameter is an integer from 0 to 255, used to identify the specific location where the error occurred. When the same custom error is triggered at multiple locations, using different state values can help developers quickly locate the problem source. For example:

-- Use state 1 in trigger A
RAISERROR('Data validation failed', 16, 1)

-- Use state 2 in stored procedure B  
RAISERROR('Data validation failed', 16, 2)

Practical Application Examples

The following is a complete trigger implementation demonstrating the application of RAISERROR in real scenarios:

Create trigger tgrInsteadTrigger on copytableto
Instead of Insert as 
    Declare @store_name varchar(30);
    declare @sales int;
    declare @date datetime;

    select @store_name = i.store_name from inserted i
    select @sales = i.sales from inserted i
    select @date = i.Date from inserted i
begin
    if (@sales > 1000)
        begin
        RAISERROR('Cannot Insert where salary > 1000',16,1); 
        ROLLBACK;
        end
    else
        begin
        insert into copytablefrom(store_name, sales, date) values (@store_name, @sales, @date);
        Print 'Instead After Trigger Executed';
        end
End

Usage in TRY-CATCH Blocks

Combining RAISERROR with TRY-CATCH structures provides more comprehensive error handling mechanisms:

BEGIN TRY
    -- Errors with severity 11-19 will jump to the CATCH block
    RAISERROR ('Error raised in TRY block', 16, 1);
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Re-throw original error information in CATCH block
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

Permission Requirements and Best Practices

When using the RAISERROR function, the following permission requirements should be noted:

• Any user can specify severity levels 0-18
• Severity levels 19-25 require sysadmin role or ALTER TRACE permission
• Levels 19-25 must use the WITH LOG option

For new application development, Microsoft recommends using the THROW statement instead of RAISERROR, as THROW has simpler syntax and is not affected by SET XACT_ABORT settings.

Message Formatting Capabilities

RAISERROR supports message formatting functionality similar to the C language printf function, allowing dynamic construction of error messages using conversion specifiers:

RAISERROR (N'User %s has exceeded sales limit with amount %d', 
    16, 1, 
    N'John', 1500);

This formatting capability makes error messages more flexible and descriptive.