How to print DateTime variable in the RAISERROR method?

Question

My Stored Procedure accepts two params @EffectiveStartDate DATETIME
@EffectiveEndDate DATETIME

I wrote the validation code as this:

IF(@EffectiveStartDate > @EffectiveEndDate)
        BEGIN
            RAISERROR ('SPName: InsertUpdateLiquidityDateRule:  Start Date: %s cannot  be greater than End Date %s',11,1,CAST(@EffectiveStartDate AS varchar(30)),CAST(@EffectiveEndDate AS varchar(30)));
            RETURN -1
        END 

May I know what am I doing wrong here.

While Compiling my SProc, it raised the message 'Incorrect syntax near CAST()'

Solution 1

The supplied value must be a constant or a variable. You cannot specify a function name as a parameter value. (from MSDN Executing Stored Procedures).

You need to do something like this:

declare @EffectiveStartDateText varchar(30)
set @EffectiveStartDateText = cast(@EffectiveStartDate as varchar)

declare @EffectiveEndDateText varchar(30)
set @EffectiveEndDateText = cast(@EffectiveEndDate as varchar)

RAISERROR (
    'SPName: InsertUpdateLiquidityDateRule:  Start Date: %s cannot  be greater than End Date %s',
    11,
    1,
    @EffectiveStartDateText,
    @EffectiveEndDateText);

Solution 2

The docs for FORMATMESSAGE say "For more information about the placeholders allowed in error messages and the editing process, see RAISERROR (Transact-SQL)." This really seems to imply that RAISERROR should work the same way as FORMATMESSAGE.

Since you can use CONVERT (but not CAST) in the parameters of FORMATMESSAGE, if the implication were true then you could use CONVERT in the parameters of RAISERROR, which would allow for a sweet and elegant one-liner.

But SQL Server doesn't work (the way you expect). However, at least it can be made a bit simpler: you only have to declare one variable, not two:

DECLARE @ErrorMessage NVARCHAR(1000);
SET @ErrorMessage= FORMATMESSAGE('SPName: InsertUpdateLiquidityDateRule:  Start Date: %s cannot  be greater than End Date %s',
    CONVERT (VARCHAR(30), @EffectiveStartDate, 23),
    CONVERT (VARCHAR(30), @EffectiveEndDate, 23)
    );

RAISERROR (@ErrorMessage, 11, 1);

(SQL Server 2016 SP2-CU12)

Erland Sommarskog (SQL Server MVP since 2001) nicely sums it up here: "I get the feeling that SQL Server is intentionally designed to be as confusing as possible". And "if you at some point runs [sic] for the door screaming Oracle, come back, everything is forgiven, I can't hardly blame you".