Displaying parameterized string in SQL Server

One of the things that I do frequently is to put together some message that involves a template filled in with variables. For example, I want my error message to fit a certain pattern. Or I want to put a meaningful log message text. In SQL Server, I repeat the following ugliness in my code:

SET @message = N'This is my message with ' 
  + Cast(@NumVariable as nvarchar(100))
  + N' as a number. Additional information:  '
  + @StringVariable;

Look at how I could represent this using the string Format method in .Net:

message = string.Format("This is my message with {0} as a number.  Additional information:  {1}",
   numVariable, StringVariable);

The code is much cleaner. I can see right away the template part and the variables. I don’t have to worry about casting numbers to a string data type.

I wish I could that in SQL Server.

But you can! Since SQL Server 2008, Transact SQL has had a FormatMessage function. This function has two groups of parameters. The first group contains the template text. The second group are all of the parameters that you want to fill variable parameter slots in the template text. I can replace my code above with the cleaner:

SET @message = FORMATMESSAGE(N'This is my message with %i as a number.  Additional information:  %s'
  , @NumVariable,  @StringVariable);

This is much easier to read. It handles type casting very naturally. It has simplified my TSQL code considerably.

About Russ

Russ Loski is a data warehouse developer focusing on SSIS and SQL Server development. He has worked with SQL Server since 1998. He has used various technologies to move data from one source to others. He has worked for well-known companies such as the Bank of America, the Dallas Cowboys Football Club, T-Mobile and Coca Cola Enterprises. Currently he is developing a Data Warehouse for a large health care organization.
This entry was posted in TSQL. Bookmark the permalink.

Leave a Reply