12

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

0

4 Answers 4

19

Use %%:

raiserror(N'This is a message with %%', 0, 1);
3
  • In that way I still need to do replace, Can I avoid using replace ?
    – adopilot
    Aug 18, 2014 at 10:05
  • Store %% in the database too, where the message comes from. Aug 18, 2014 at 10:15
  • Works with FORMATMESSAGE as well Dec 12, 2017 at 17:54
10

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 %')

4

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
1

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.