What is the syntax meaning of RAISERROR()
RAISERROR() swiftly triggers a custom error in SQL Server, disrupting the execution flow depending on severity. The syntax is RAISERROR('message', severity, state). Severity ranges between 0 (info) to 25 (fatal), whereas the state helps to pinpoint the error’s origin.
Demonstration:
Here, severity 16 denotes a user-fixable runtime error. Exploring this powerful error management feature can prove beneficial in handling exceptions and dictating the control flow in complex T-SQL codes.
Severity and state - The dynamic duo
The implication of severity levels
Severity levels from 0-10 are synonymous with alerts or informational messages, harmless to the code execution. Ranging 11-16, these represent user-correctable errors, forcing the execution into the CATCH block. Crossing the line into 17-25, these symbols represent software or hardware errors, so severe, even execution might take its last breath!
State - GPS for your errors
The state parameter, accepted as an integer from 0 to 255, acts as a pinpointing tool for locating the precise origin of errors inside your stored procedures or triggers. Like a good detective, different states for the same error identify the culprit hiding inside your code.
Sysadmin - The chosen one for high severity
Severity levels beyond 25, specifically from 33 to 60, are like top-secret codes for the sysadmin role or users having the ALTER TRACE permission. Use these finders in dire situations, to signal catastrophic issues within the system!
Crafting RAISERROR the right way
Creating a meaningful ruckus
This sample creates a custom error of severity 16, representing a breach of business norms that the end-user can fix.
Using RAISERROR in triggers
Incorporate RAISERROR in your triggers to ensure adherence to syntax, and to generate flags signaling violations or malignant states.
Embracing RAISERROR in CATCH block
Executing RAISERROR within a CATCH block to rethrow or log the original error, for future reference, is a good practice. It effectively retains the original error number, severity, and state.
RAISERROR - walking the tightrope
Selecting severity for business logics
Stick with moderately severe levels (11-19) for enforcing business rules, without disturbing the application's steady heartbeat. Extreme levels should be strictly reserved for indicating non-recoverable system errors.
Separating twin error locations
Assign distinct state numbers to separate identical error instances across varying stored procedures or functions. This provides a potent diagnostic aid and debugging tool.
Maintaining a game plan for errors
Ensure consistency in defining custom errors using sys.messages and reference them using RAISERROR for better manageability and easier debugging.
References
Was this article helpful?