Categories
raiserror sql sql-server sql-server-2005 stored-procedures

How to print DateTime variable in the RAISERROR method?

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()’

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