Conditional Filtering in SQL

Assume I need to select a set of data based on the following criteria.


  • If the @Active-Items-Only flag is set, then select only the active items
  • otherwise select all active and non-active items.
DECLARE @ActiveOnly bit = 1; -- select only active

SELECT [HotelContractID]
      ,[RoomTypeID]
      ,[TravelStart]
      ,[TravelEnd]
      ,[ReleaseTypeID]
      ,[ReleaseValue]
      ,[Active] 
  FROM [dbo].[RoomAllocation]
  WHERE [HotelContractID] = 124 AND
  (CASE
    -- if active only selected, 
    -- check only active = 1 rows in the table and return 1
    WHEN  @ActiveOnly = 1 AND (Active = 1) THEN 1 
    -- if active and inactive both selected, 
    -- check for both active = 1 and active = 0 rows in the table and return 1
    WHEN  @ActiveOnly = 0 AND (Active = 1 OR Active = 0) THEN 1
  END) = 1 -- 1=1 where conditional always true. magic happens in WHEN condition.
Results:

When you set @ActiveOnly = 1, it gives only the active ones.
When you set @ActiveOnly = 0, it gives only the active and inactive both.

Popular posts from this blog

Print a receipt using a Thermal Printer with C#.NET

Automatic redirect upon session timeout using ASP.NET MVC and Javascript

Complex Master-Detail Form using Knockout.js and ASP.NET MVC