Value plus null

I have the following procedure running on SQL SERVER 2005

ALTER proc [dbo].[p_shipavgAddOnUpdate]
as
begin
declare @addon decimal(20,2),@FloorPrice decimal(20,2)

update z
set  
@addon = case when pp.mapprice>0
        then 0
        else            
           ( BasePrice + Mktng ) *.027 
-- to allow for a higher sale price than the base price
        end
    ,addon=@addon
    ,@FloorPrice = ( BasePrice + Mktng +  @AddOn )
    ,@FloorPrice =

  CASE WHEN @FloorPrice > COALESCE(pp.mapPrice, 0) 
    THEN  @FloorPrice 
    ELSE    .mapPrice 
  END
,FloorPrice = @FloorPrice
,FloorOffer  = @FloorPrice + FinalShippCost
FROM zshipaverage z
     inner join products p on z.sku = p.sku
        inner join product_pricing pp on p.productid=pp.product
end

then the proc gets called from another proc, which runs a bunch of procs, ie

exec proc1
exec proc2

.... etc

in one proc previous to this one, the baseprice field is set in the next proc, the Mktng field is set in this proc the addon, and floorprice are set so the floorprice is suppose to be = baseprice + Mktng + Addon after running the proc i ended with a record as so:

baseprice = 6.14
mktng     = 2.13
addon     = NULL
floorprice= 0.00

Two questions; 1.- how can i end with a floorprice of 0.00 if addon is null? (nromal ansi default开发者_StackOverflow set to value + NULL = NULL)

I can't reproduce the effect

2.- Am i assured that the procedures in the call proc are running in order?

thnx

It's right there in your code

@FloorPrice =
CASE WHEN  
@FloorPrice > COALESCE(pp.mapPrice, 0) THEN
@FloorPrice ELSE    .mapPrice END

You are NULL checking the pp.mapPrice before setting the value of @FloorPrice which means that @FloorPrice will be 0 if any of its constituents are NULL

You might also want to replace the other section as follows:

, @FloorPrice = (ISNULL(BasePrice,0) +
                 ISNULL(Mktng,0) + 
                 ISNULL(@AddOn,0) )

Try this:

Correction:

, @FloorPrice = CASE WHEN @AddOn IS NULL THEN 0 ELSE ( BasePrice +
              Mktng+ 
              @AddOn ) END