SQL: Make string with FORMATMESSAGE
Author: jason
Date: 2019-01-17 11:08:56
Category: Technical

According to Microsoft, this was introduced in SQL 2008. FORMATMESSAGE lets you build a nvarchar string from parameters/variables, which is pretty cool

https://www.mssqltips.com/sqlservertip/4108/tsql-enhancements-introduced-in-sql-server-2016/

https://docs.microsoft.com/en-us/sql/t-sql/functions/formatmessage-transact-sql?view=sql-server-2017

Code


/*
Format Message builds an NVARCHAR String
Use %s as the placeholders for the string you want to put in
NULL values will come out as (null), which isn't very cool...
*/

DECLARE @BuildMe AS NVARCHAR = '';
DECLARE @Info AS VARCHAR(255) = 'Meep Meep';
DECLARE @More AS CHAR(10) = 'Brown Fox';
DECLARE @Null AS VARCHAR(10) = NULL;

SELECT FORMATMESSAGE('This is the %s and this is the %s. The road runner says %s. And the %s jumps around. With %s'
, 'first variable'
, 'second variable'
, @Info
, @More
, @Null) AS Result;







jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009