Conditional where clause in SQL server and tricks

Suppose you want to search the records on the basis of start and end date and for that if ModifiedDate is not null then use it otherwise use the Creation date:

Simply we can use, if in where clause like this

If ModifiedDate IS NULL
       Where CreationDate Between @StartDate And @EndDate
Else    
        Where ModifiedDate Between @StartDate And @EndDate

If there would be more similar condition then you would have to use more if else and same code will be written again and again, so what is the solution, see if this

Where ISNULL(ModifiedDate, CreationDate)  Between @StartDate And @EndDate

Isn't it clear and easy

Suppose you have an Inventory table in which there is a column status and it keeps 3 values

10: In Hand    
20: Re Order Level    
30: Sold Out

And you also have to give the option to select all the records, say the @status = 0,

if parameter value is 10 or 20 or 30 then you have to return only those types of records but if it is 0 then you have to return all the records. How will you write your procedure, Let me write in a very simple way

Create PROC Inventory_Search
    @Status INT
AS
    Select ………………………………
From Inventory I
Where  @Status = 0 OR I.Status = @Status

Are you getting what it will do, let me explain

For @Status = 10 it will become

10 =0 or I.Status = 10

So 10 = 0 will never be true and only those records will be returned where Status = 10, similarly for 20 and 30.

For @Status = 0 it will become0

0 =  0 OR I.Status = 0

So 0 = 0 will always be true and will return all the records.

Ali Adravi Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
  • sql server
By Ali Adravi On 14 Jul, 12  Viewed: 610

Other blogs you may like

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi   On 02 May 2013  Viewed: 783

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi   On 31 Mar 2013  Viewed: 844

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 2,408

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 582

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 11,100