How to escape '%' inside varchar @params for RAISERROR funcion in SQL Server 2012

Question

How I should escape the % character in params so my RAISERROR retun my message

declare @msg varchar(max)  = (SELECT ' Message with %  ' AS MSG)

if @msg is not null
begin
    RAISERROR (@msg ,16,1); 
end

This will rise error with message

Msg 2787, Level 16, State 1, Line 4
Invalid format specification: '% '.

For end users this message is unreadable.

My messages is generated from database and setting this message.

Way I do avoid getting wrong message is replacing

set @msg = REPLACE(@msg,'%','P')

But I still do not know how to add % sign

Answer 1

Use %%:

raiserror(N'This is a message with %%', 0, 1);

Answer 2

There's a simple, neat way to work around the percentage sign problem. Use the message text as parameter:

RAISERROR ('%s', 16, 1, 'Message with %')

Answer 3

As you are using SQL 2012, you could (should?) be using the new THROW syntax, eg

BEGIN TRY

    RAISERROR ( 'dummy', 16, 1 )

END TRY
BEGIN CATCH

    DECLARE @msg NVARCHAR(2048) = ( SELECT 'Message with %% ' AS MSG );

    THROW 51000, @msg, 1;

END CATCH

Although THROW doesn't like percentage signs (%) either so you still have to escape it, presumably because you can use FORMATMESSAGE with it. % signs on their own cause a headache for FORMATMESSAGE too as they're basically placeholders for parameters you pass in. So my honest advice would be to simply use the 'percent' word instead:

USE tempdb

EXEC sys.sp_addmessage
      @msgnum   = 60001
    , @severity = 16
    , @msgtext  = N'I am 99.99999% this will NOT work.' -- OK this definitely won't work
    , @lang = 'us_english'
    , @replace = 'replace'
    GO

DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(60001, 'test1' ); 

THROW 60001, @msg, 1; 
GO

EXEC sys.sp_addmessage
      @msgnum   = 60001
    , @severity = 16
    , @msgtext  = N'Even if you escape it, the sign  %%  disappears...' -- Even if you escape it
    , @lang = 'us_english'
    , @replace = 'replace'
    GO

DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(60001, 'test1' ); 

THROW 60001, @msg, 1; 
GO


EXEC sys.sp_addmessage
      @msgnum   = 60001
    , @severity = 16
    , @msgtext  = N'I am 100 percent sure this will work.'
    , @lang = 'us_english'
    , @replace = 'replace'
GO

DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(60001, 'test1' ); 

THROW 60001, @msg, 1;
GO


-- Tidy up
EXEC sys.sp_dropmessage 60001

Answer 4

Another, very clean, way would be to create a variable e.g.:

declare @PctChar char(1) = '%'

then use that variable in formatmessage or raiserror, as:

raiserror('98%s',0,1,@PctChar);