Concatenate Message In RAISERROR

Go To StackoverFlow.com

28

What's the proper syntax here?

If (@timestamp < (Select PromoStartTimestamp From @promo))
    RAISERROR('Code not valid until ' + (Select PromoStartTimestamp From @promo)
              ,16
              ,1);

I've tried:

If (@timestamp < (Select PromoStartTimestamp From @promo))
    RAISERROR(N'Code not valid until @starttimestamp'
              ,16
              ,1
              ,(Select PromoStartTimestamp From @promo));
2012-04-05 17:07
by Greg


47

You can use %s as a string substitution parameter in RAISERROR:

DECLARE @PromoStartTimestamp DATETIME
DECLARE @PromoStartTimestampString VARCHAR(50)

SELECT @PromoStartTimestamp = PromoStartTimestamp From @promo
SELECT @PromoStartTimestampString = CAST(@PromoStartTimestamp AS VARCHAR)

If (@timestamp < @PromoStartTimestamp)
    RAISERROR(N'Code not valid until %s'
              ,16
              ,1
              ,@PromoStartTimestampString);
2012-04-05 17:09
by Michael Fredrickson
I get an error when I try to Cast(@promostarttimestamp as varchar) saying Incorrect syntax near 'Cast'. Expecting Select or ( or when I don't Cast I get Cannot specify datetime data type (parameter 4) as a substitution parameter.Greg 2012-04-05 17:21
+1 for %s but you cannot use expressions (CAST) in the RAISERROR parameters. It has to be isntead RAISERROR(N'Code not valid until %s',16 ,1 ,@PromoStartTimestampCastedToString));Remus Rusanu 2012-04-05 17:34
D'oh! Thanks @RemusRusanu... should work better now - Michael Fredrickson 2012-04-05 17:39
You can also add multiple variables eg RAISERROR('Order %s does not have any %s.',16,1, @SalesOrderNo,@CurrentItemNr) - SteveCav 2016-02-25 00:43
Ads