Fetch one or all record from table conditionally.

WHAT WE WILL WE LEARN IN THIS POST?

After reading this post we must be able to fetch one or all record from the table conditionally. We will fetch one record based on the condition mentioned in where clause or all records when a particular value is to be retrieved. i.e., we can either select one record of our choice or all records when we choose some particular value. This is useful when we need to fetch single record or to display all records to the user. Either show selected record or all records.

HOW WE ARE GOING TO ACHIEVE THIS?

We will use most common, simple select statement and some variables along with Boolean Logic. Why Boolean Logic? Because we won't use case or if else statement.

Lets begin our excursion...

Fetching all records form the table is simple. We just need to fire select statement.

ASSUMPTIONS:

ONE TABLE: PRODUCT MASTER

FIELDS:

ProductId

ProductName

ProductDesc

ProductCode,

BarCode

Price

Discount

Category

SubCategory

The Statement: Select * from ProductMaster will fetch all records from the ProductMaster table.

We will use the same query as mentioned above but with where clause.

Step 1: Declare a variable as @AllProduct of type varchar(20) and a variable @Product of type varchar(20). @AllProduct will contain ProductId of the product on the selection of which, all products will get retrieved. @Product will contain ProductId of the single product that the user would like to choose.

Step 2: Assign ProductId to the variable @AllProduct from ProductMaster table where  ProductDesc is 'All'.

Step 3: Now select all records from the ProductMaster table where ProductId is equal to @Product or @AllProduct equal to @Product. So, How is it going... first condition is simple where clause. In second condition, we are just making condition 1=1. i.e., when @AllProduct will be equal to @Product that is selected productId is same as that productId with description 'All' in ProductMaster table.

So, either 1=1 will be true fetching all records or ProductId=@ProductId getting single selected record.

Here is the complete query:

DECLARE @AllProduct varchar(20)

DECLARE @Product varchar(20)=26

SELECT @AllProduct = ProductId FROM 
ProductMaster WHERE ProductDesc='All';
SELECT * from ProductMaster
WHERE (@AllProduct =@Product) or (ProductId = @Product)

ENHANCED VERSION OF THE ABOVE QUERY

Above query will work as same like previous one except with a difference that it will fetch all records when user does not select any product at all.

DECLARE @AllProduct varchar(20)

DECLARE @Product varchar(20)=26

SELECT @AllProduct = ProductId FROM
ProductMaster WHERE ProductDesc='All';

SELECT * from ProductMaster
WHERE (@AllProduct =@Product) OR (ProductId = @Product) OR (@Product IS NULL and 1=1)

USE:

We can use this type of query during displaying all records to the user with a search functionality. Either search on single record or get all records.

CONCLUSION

We have seen how to retrieve one or all records from the table using simple select statement, where clause and boolean logic. We have tried to omit case or if conditions because that will hamper query performance, and Boolean logic is simple and easy to understand rather than using case and if making it complex.

FEEDBACK

Hope the post will be useful for you in your programming career. What I need from you is to comment below and share with me your doubts and/or suggestions. Please do like and share the post on all possible social networking platform. So that the post may reach newbie and talented generations.  Thank you :) Have a wonderful programming.