SQL Server – How to Output New Line with FORMATMESSAGE

sql server

The following code stats that, how many users that has created at least one post on a specific SE site.

DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(DISTINCT OwnerUserId) 
FROM Posts where OwnerUserId is not null)
PRINT FORMATMESSAGE('%s has \r\n %d users', DB_NAME(), @RowCount+1)

which basically works though, the FORMATMESSAGE part does not work as expected.

Usually, \n (in lots of systems and languages) and \r\n (mainly in MS systems) represent new line. I tried both, and even /r/n, none of them worked.

How can I output a new line with FORMATMESSAGE in transact sql?

Best Answer

The problem is the interpretation of \r\n as carrriage return and line feed. SQL Server does interpret \r\n like this in some places, but the FORMATMESSAGE function is not one of them, so instead you need to use CHAR(13) and CHAR(10) which represents the the ASCII codes for Carriage Return (13) and Line Feed (10):

DECLARE @RowCount INT
SET @RowCount = (SELECT 10)
PRINT FORMATMESSAGE('%s has ' + CHAR(13) + CHAR(10) + ' %d users', DB_NAME(), @RowCount+1)

Or use a variable:

DECLARE @RowCount INT,
    @NewLine CHAR(2) = CHAR(13) + CHAR(10)
SET @RowCount = (SELECT 10)
PRINT FORMATMESSAGE('%s has ' + @NewLine + ' %d users', DB_NAME(), @RowCount+1)
-- or
DECLARE @RowCount INT,
    @NewLine CHAR(2) = CHAR(13) + CHAR(10)
SET @RowCount = (SELECT 10)
PRINT FORMATMESSAGE('%s has %s %d users', DB_NAME(), @NewLine, @RowCount+1)
Related Question