title: Using ISNULL in where clause
body: <pre><code>DECLARE @use10percent INT
  
  SELECT 
      @use10percent = COUNT(*) 
  FROM 
      [dbo].[CUSTOMER_ORDER] o
  INNER JOIN 
      [dbo].[CUSTOMER] c ON c.ID = o.CUSTOMER_ID
  INNER JOIN 
      [dbo].[CUST_ADDRESS] ca ON ca.CUSTOMER_ID = o.CUSTOMER_ID 
                              AND ca.ADDR_NO = o.[SHIP_TO_ADDR_NO]
  WHERE 
      o.[ID] = @CUST_ORDER_ID
      AND (isnull(ca.STATE, c.STATE) IN ('AB','BC','MB','MT','NB','NS','ON','PQ','QC','SK','TX','NY'))
  
  /**/
  UPDATE SHIPPER_LINE
  SET ACT_FREIGHT = CONVERT(DECIMAL(10, 2),
                       (CASE
                           WHEN CAST(ShipWeight AS Float) &gt;= 400 
                             THEN CAST(Rate AS Float) * 1.06
                           WHEN CAST(ShipWeight AS Float) &lt; 400 
                             THEN CASE
                                      WHEN @use10percent = 0 
                                        THEN CAST(Rate AS Float) * 1.20
                                      ELSE CAST(Rate AS Float) * 1.10 --New rate for Canada, TX, and NY
                                  END
                           ELSE CAST(Rate AS Float)
                         END))
  </code></pre>
  
  <p>The goal of this trigger is to add 10% to the freight charge when the weight is under 400lbs and the destination state is NY, TX, or all of Canada. It works if the customer uses a drop ship address (ca.state) and I get a 10% increase. But if the customer is the buyer and destination, the (ca.state) is null and the (c.state) needs to be used. The code above adds the full 20% if the (ca.state) is null and the (c.state) is NY, TX, or Canada. </p>
  
  <p>How can I fix the where clause to filter correctly? </p>