Displaying parameterized string in SQL Server

Leave a Comment / TSQL / By

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.

Leave a Reply