In SQL Server, the FORMATMESSAGE function is used to create formatted error messages dynamically by replacing placeholders in a string with specified values. This is particularly useful when you need to generate custom error messages that can include variable content.
Syntax of FORMATMESSAGE
FORMATMESSAGE ( string_format , [ argument1, argument2, ... n ] )
- string_format: A string that can contain placeholders for formatting. Placeholders are specified using %n, where n is the position of the argument.
- argument1, argument2, ... n: The values that will replace the corresponding placeholders in the string.
Creating Custom Error Messages
You can use FORMATMESSAGE in conjunction with the RAISEERROR statement to generate and raise custom error messages. Here’s how you can do that:
1. Define the Format Message: Use FORMATMESSAGE to create the error message with placeholders.
2. Raise the Error: Use RAISEERROR combined with the formatted message.
Example
Here’s an example that shows how to create and raise a custom error message:
DECLARE @ErrorCode INT = 50001;
DECLARE @Message NVARCHAR(200);
DECLARE @UserName NVARCHAR(100) = 'JohnDoe';
DECLARE @TransactionId INT = 12345;
-- Create the formatted error message
SET @Message = FORMATMESSAGE('User %s attempted to access transaction %d, but the access was denied.', @UserName, @TransactionId);
-- Raise the error with a custom message
RAISEERROR(@Message, @ErrorCode, 1);
Breakdown of the Example
1. Variable Declaration: Some variables are declared to hold the error code, user name, and transaction ID.
2. Generating the Message: FORMATMESSAGE is used to create a message that includes the user name and transaction ID.
3. Raising the Error: The RAISEERROR function is called with the formatted message, an error code, and a severity level (1 in this case).
Important Considerations
- The error code must be a user-defined error number greater than 50000 to be considered a custom error.
- The RAISEERROR function can also be used with a severity level to indicate the seriousness of the error (from 0 to 25).
- You can also log the error or handle it in a try-catch block, depending on your error handling strategy.
Using FORMATMESSAGE effectively allows for more informative and user-friendly error messages in your SQL Server applications.