Mastering FORMATMESSAGE in SQL Server 2016: Ad-Hoc String Formatting

Introduction

In earlier versions of SQL Server, the FORMATMESSAGE function was strictly tied to the sys.messages catalog. If you wanted to format a string with variables, you first had to add a custom message using sp_addmessage.

SQL Server 2016 removed this roadblock. Now, FORMATMESSAGE supports ad-hoc message strings, allowing you to build formatted text on the fly. This enhancement is a massive productivity boost for DBAs and Developers who need to generate dynamic logs, error messages, or reports without polluting the system catalog.

---

SQL Server 2016 Breakthrough: Ad-Hoc Formatting

The most critical update is that the first argument of the function can now be a string literal (the template) instead of just a message ID number.

Syntax Comparison

• Pre-2016: FORMATMESSAGE ( msg_id , [ param1 [ ,...n ] ] )
• SQL Server 2016+: FORMATMESSAGE ( 'msg_string' | msg_id , [ param1 [ ,...n ] ] )

Example: Building a Dynamic String

Here is how you can use the new ad-hoc capability to build a brand-consistent message for MyTechMantra:

DECLARE @ExampleMessage VARCHAR(150)

SET @ExampleMessage = FORMATMESSAGE ('SQL Server %s, has some amazing new features and learn 
them on %s!', '2016', 'MyTechMantra.com');

SELECT @ExampleMessage;

Output

Why Use FORMATMESSAGE instead of Concatenation?

Many developers still use the + operator or CONCAT() to build strings. However, FORMATMESSAGE is superior for several reasons:

1. Readability: The template remains intact, making it easy to see the final structure.
2. Data Type Safety: It handles the conversion of various data types into the string template automatically.
3. Localization: It is built to work with language-specific formatting if you eventually move to sys.messages.

---

FORMATMESSAGE vs. RAISERROR vs. THROW

It is important to understand where this function fits in your error-handling stack.

Conclusion

The addition of ad-hoc strings to FORMATMESSAGE is a significant quality-of-life improvement for SQL Server developers. By removing the dependency on sys.messages for simple string formatting, SQL Server 2016 allows for cleaner code, easier debugging, and more flexible error handling. Whether you are building dynamic logs or preparing messages for the THROW statement, FORMATMESSAGE is the most robust tool for the job.

Next Steps

We are almost at the end of our journey! Join us for the final installment, Part 10, where we explore the enhancements made to the SERVERPROPERTY Function in SQL Server 2016 to help you retrieve even more metadata about your instances.

Read Next: Part 10: SERVERPROPERTY T-SQL Enhancement in SQL Server 2016

Frequently Asked Questions (FAQs)

1. Can I still use message IDs from sys.messages?

Yes. The 2016 version is backward compatible. You can pass either a msg_id (INT) or a msg_string (VARCHAR/NVARCHAR) as the first argument.

2. What is the maximum length of the message string?

The msg_string argument can be up to 2,047 characters.

3. Does FORMATMESSAGE support %i or %u?

Yes, it supports standard C-style format codes like %s (string), %d or %i (signed integer), and %u (unsigned integer).

4. Can I use FORMATMESSAGE inside a stored procedure?

Absolutely. It is highly recommended for building the message string that you then pass to the THROW command.

5. What happens if I provide fewer arguments than the template requires?

The function will return NULL. Always ensure the number of arguments matches the number of placeholders in your string.

6. Is there a performance penalty for using ad-hoc strings?

No. In fact, it is often faster than querying sys.messages because it avoids a system table lookup.

7. Does it support Unicode characters?

Yes, if you use the N prefix (e.g., N'Message text'), it supports NVARCHAR and Unicode characters.

8. Can I use it for logging to a table?

Yes, it is an excellent tool for formatting audit log entries before inserting them into a table.

9. Why did Microsoft wait until 2016 to add this?

While we can’t speak for Microsoft, the move toward JSON and more flexible application-tier logic necessitated more “inline” string power in T-SQL.

SQL Server 2016 T-SQL Enhancement Series

• SERVERPROPERTY Function
• TRUNCATE TABLE WITH PARTITIONS
• DROP IF EXISTS
• ALTER TABLE WITH (ONLINE = ON | OFF)
• MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE
• ALTER DATABASE SET AUTOGROW_ALL_FILES
• COMPRESS and DECOMPRESS Functions
• STRING_SPLIT and STRING_ESCAPE Functions
• FORMATMESSAGE Statement

Trending SQL Server Articles and Tips