SQL Shortcut Tip

In creating a SQL select statement to query some records from a table, it is pretty common to include WHERE clause - where you are limiting your selections by criterias specified.

Here is an example:

SELECT *
FROM Products
WHERE CategoryID = 5

The SQL statement above will return all Product records that are in Category 5.

Now, sometimes we want to use the condition only if it meets certain condition (like if the condition value is not empty or null).

So you SQL statement may look like this (assuming this is wrapped in a stored procedure) - where you want the stored procedure to return all Products if Category is not filtered:

ALTER PROCEDURE [dbo].[GetProducts] 
@CategoryID int = NULL 
AS

IF (@CategoryID IS NULL)
BEGIN   
SELECT *   
FROM dbo.Products
END
ELSE
BEGIN   
SELECT *   
FROM dbo.Products   
WHERE CategoryID = @CategoryID 
END  

Looks rather long isn't it? In LINQ this is a bit shorter, but still quite repetitive:

var data;
if (categoryId == 0)
   data = from row in db.Productselse
   data = from row in db.Products where CategoryID == categoryId 

So how can we short-cut this into shorter statements that is elegant and still do the job well?

Like this:

ALTER PROCEDURE [dbo].[GetProducts]
  @CategoryID int = NULL
AS   

SELECT *   
FROM dbo.Products   
WHERE (@CategoryID is NULL OR @CategoryID = 0) OR CategoryID = @CategoryID 

In LINQ:

var data = from row in db.Products
           where (CategoryID == categoryId  || categoryId == 0)

By including the possibility of null or zero (or any default condition) in the WHERE clause means that the condition will be computed as well to produce the results. Got it?

-- read more and comment ...