ISNULL SQL function

1. Usage of ISNULL to show Stock Status and Quantity that needs to be order.

SQL Server Query 1

            
 SELECT  

     ProductID,  

     ProductName,  

     UnitsInStock,  

     UnitsOnOrder,  

     ReorderLevel,  

     StockStatus =  

         CASE  

             WHEN UnitsInStock > 0 THEN 'In Stock'  

             ELSE 'Out of Stock'  

         END,  

     --  Prioritize UnitsOnOrder, then ReorderLevel, then 0  

     QuantityToOrder = ISNULL(COALESCE(UnitsOnOrder, ReorderLevel), 0)  

 FROM Products  

 ORDER BY ProductID;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("ProductID", "ProductName", "UnitsInStock", "UnitsOnOrder", "ReorderLevel")  

     .Column(new CASE()  

         .When(new Column("UnitsInStock").GreaterThan(0))  

         .Then("In Stock").Else("Out Of Stock")  

     , "StockStatus")  

     .Column(new ISNULL(new COALESCE(new Column("UnitsOnOrder"), new Column("ReorderLevel")), 0), "QuantityToOrder")  

     .From("Products")  

     .OrderBy(new OrderBy().Set(new Column("ProductID")))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT ProductID,
       ProductName,
       UnitsInStock,
       UnitsOnOrder,
       ReorderLevel,
       CASE WHEN UnitsInStock > @pMAIN_2507200144082669950 THEN @pMAIN_2507200144082669951 ELSE @pMAIN_2507200144082669952 END AS StockStatus,
       ISNULL(COALESCE (UnitsOnOrder, ReorderLevel), @pMAIN_2507200144082669953) AS QuantityToOrder
FROM Products
ORDER BY ProductID ASC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of ISNULL to show Units in Stocks and to Order.

SQL Server Query 2

            
 SELECT  

     ProductID,  

     ProductName,  

     UnitPrice,  

     UnitsInStock,  

     UnitsOnOrder,  

     --  Handle NULL UnitsInStock or UnitsOnOrder  

     InventoryValue = UnitPrice * ISNULL(UnitsInStock, 0) + UnitPrice * ISNULL(UnitsOnOrder, 0)  

 FROM Products  

 ORDER BY ProductID;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select().Columns("ProductID", "ProductName", "UnitPrice", "UnitsInStock", "UnitsOnOrder")  

     .Column(new ColumnArithmatic("UnitPrice").MULTIPLY(new ISNULL(new Column("UnitsInStock"), 0))  

         .ADD(new Column("UnitPrice")).MULTIPLY(new ISNULL(new Column("UnitsOnOrder"), 0)), "InventoryValue")  

     .From("Products")  

     .OrderBy(new OrderBy().Set(new Column("ProductID")))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT ProductID,
       ProductName,
       UnitPrice,
       UnitsInStock,
       UnitsOnOrder,
       UnitPrice * ISNULL(UnitsInStock, @pMAIN_2507200144082725220) + UnitPrice * ISNULL(UnitsOnOrder, @pMAIN_2507200144082725221) AS InventoryValue
FROM Products
ORDER BY ProductID ASC;


            
        

Parameters (If used)

Query Results 2: