Case statement always skipping to ELSE in stored procedure

Question

I am trying to use CASE statement during the insert in sql Stored procedure like this:

INSERT INTO [dbo].[OfferPrice]
            (OfferId,Price,DefaultPrice,SalePrice,
             SaleFromDate,SaleToDate)
SELECT tvp.OfferId   AS OfferId,
       CASE
         WHEN @Price IS NOT NULL THEN @Price
         WHEN @Price IS NULL
              AND tvp.SalePrice IS NOT NULL
              AND Getutcdate() >= tvp.SaleFromDate
              AND Getutcdate() < tvp.SaleToDate THEN tvp.SalePrice
         WHEN @Price IS NULL
              AND tvp.SalePrice IS NULL THEN tvp.DefaultPrice
         ELSE 0
       END AS Price,
       tvp.DefaultPrice AS DefaultPrice,
       tvp.SalePrice AS SalePrice,
       tvp.SaleFromDate AS SaleFromDate,
       tvp.SaleToDate AS SaleToDate
FROM   @OfferPriceTVP tvp
       LEFT JOIN [dbo].OfferPrice dop
              ON dop.OfferId = tvp.OfferId
WHERE  dop.OfferId IS NULL

Problem is that CASE is always skipping to the ELSE even if previous statements are true. What am I doing wrong?

EDIT:

This is @OfferPriceTVP:

CREATE TYPE [dbo].[TVP_OfferPrice] AS TABLE
(
    OfferId INT NOT NULL PRIMARY KEY, CountryId INT NOT NULL, VatRateId INT, DefaultPrice decimal(16, 4), SalePrice decimal(16, 4),
    SaleFromDate datetime, SaleToDate datetime
);

And here the insert I was trying to do now (even though there are no dates it should set price to default right?):

DECLARE @OfferPriceTVP AS [dbo].[TVP_OfferPrice]
INSERT INTO @OfferPriceTVP (OfferId,CountryId,VatRateId,DefaultPrice,SalePrice,SaleFromDate,SaleToDate)
VALUES (10006805,2,1,1,1,NULL,NULL),
(10006806,1,1,2,1,NULL,NULL),
(10006807,1,1,3,1,NULL,NULL),
(10006808,1,1,4,1,NULL,NULL),
(10006809,1,1,5,1,NULL,NULL),
(10006810,1,1,6,2,NULL,NULL);
EXEC [dbo].[TVP_OfferPrice] @OfferPriceTVP;
GO

without seeing the data nothing can be suggested. FYI `case` will goto `else` part only when all the condition failed — Pரதீப், Jan 13 '15 at 08:08
It would seem that the two most important things for us to know about to reason about this `CASE` *expression* would be the contents of the `@Price` and `@OfferPriceTVP` variables - which you haven't shown us. — Damien_The_Unbeliever, Jan 13 '15 at 08:10
@Damien_The_Unbeliever It's obvious that `@Price` is null and `tvp.SalePrice` isn't otherwise one of the other cases would run. This means that the date check fails - `GETUTCDATE()` doesn't fall within the recorded Sales period. — Panagiotis Kanavos, Jan 13 '15 at 08:18
The important values are the contents of `SaleFromDate`,`SaleToDate`. Do they contain valid periods that cover the time the query is run? Are the periods recorded using UTC times or is there an offset mismatch that caused problems for the first few hours of the sales period? — Panagiotis Kanavos, Jan 13 '15 at 08:21
I do not always pass Date (this is important) and then value should be set to default. But even with correct dates it is not workin — kali, Jan 13 '15 at 08:22
Why would it use the default when part of the condition for that is `AND tvp.SalePrice IS NULL` and all of the supplied values seem to have a non-null `SalePrice`? — Damien_The_Unbeliever, Jan 13 '15 at 08:22

score 1 · Answer 1 · answered Jan 13 '15 at 08:25

I think, perhaps, that your intention if there are no sales dates is to take either the sale price (if available) or the default price.

If that's the case, then just omit the SalePrice IS NULL check in the third WHEN and use COALESCE:

   CASE
     WHEN @Price IS NOT NULL THEN @Price
     WHEN @Price IS NULL
          AND tvp.SalePrice IS NOT NULL
          AND Getutcdate() >= tvp.SaleFromDate
          AND Getutcdate() < tvp.SaleToDate THEN tvp.SalePrice
     WHEN @Price IS NULL
          THEN COALESCE(tvp.SalePrice,tvp.DefaultPrice)
     ELSE 0
   END AS Price

score 0 · Accepted Answer · answered Jan 13 '15 at 08:23

0

Comparisons to NULL are always false. Your table contains no sales dates. The Sales period check

Getutcdate() >= tvp.SaleFromDate AND Getutcdate() < tvp.SaleToDate

will always fail.

This means that if @Price is NULL and SalePrice isn't, only the ELSE statement is valid.

answered Jan 13 '15 at 08:23

Panagiotis Kanavos

120,703
13
188
236

my bad I guess to early in the morning – kali Jan 13 '15 at 08:25

Case statement always skipping to ELSE in stored procedure

2 Answers2