Conditional results from SQL Server CASE statements
===

Is it possible to do a boolean compare in the when-expression on a case-expression within a SQL Server case statement. I have a SELECT statement that has logic similar to the following:

    SELECT T1.ProductNumber,
        CASE
            WHEN (SELECT SUM(T2.Quantity) FROM Table2 AS T2 WHERE T2.Product=T1.Product) IS NULL
                THEN T1.Quantity
            WHEN (SELECT SUM(T2.Quantity) FROM Table2 AS T2 WHERE T2.Product=T1.Product) > T1.Quantity
                THEN T1.Quantity
            ELSE T1.Quantity - (SELECT SUM(T2.Quantity) FROM Table2 AS T2 WHERE T2.Product=T1.Product)
    FROM Table1 AS T1

This seems like a lot of duplicated lookups on Table2. If possible I'd like to do something like this:


    SELECT T1.ProductNumber,
        CASE (SELECT SUM(T2.Quantity) FROM Table2 AS T2 WHERE T2.Product=T1.Product) as altQty
            WHEN altQty IS NULL
                THEN T1.Quantity
            WHEN altQty > T1.Quantity
                THEN T1.Quantity
            ELSE T1.Quantity - altQty
    FROM Table1 AS T1
