SQL Query: If condition A is True then WHERE clause A, If condition A is False then WHERE clause B

Scenario:

The following query check parameter value and depending on the parameter value they check different WHERE clause but it select same set of columns. The problem with the following approach is that it returns two different result set where as returning same columns.

IF @ValueParameter=’VBC1′ and @DateParameter=’DBC1′ THEN

Select ColA, ColB

From TableStudent

Where TableB.Value>@valueParemeter AND TableB.EventDate>@DateParameter

ELSE IF @ValueParameter=’VBC2′ and @DateParameter=’DBC2′ THEN

Select ColA, ColB

From TableStudent

Where TableB.Value>@valueParemeter AND TableB.Value<@N AND TableB.EventDate<@DateParameter

 

Solution:

  1. Combine the IF condition and related WHERE condition using the AND predicate
  2. Combine the ELSE IF condition and related WHERE condition using the AND predicate
  3. Combine the IF and ELSE IF using the OR clause

Select ColA, ColB

From TableStudent

WHERE (@ValueParameter=’VBC1′ and @DateParameter=’DBC1′ AND TableB.Value>@valueParemeter AND TableB.EventDate>@DateParameter)

OR (@ValueParameter=’VBC2′ and @DateParameter=’DBC2′ AND TableB.Value>@valueParemeter AND TableB.Value<@N AND TableB.EventDate<@DateParameter)

It will return only single result set and query will be faster because we have used boolean logic rather than IF ELSE.

 

Reference:
SELECT  *
FROM    [Query]
WHERE   (@Parameter = 1 AND Column1 = 8)
OR      (@Parameter = 2 AND Column2 = 8)
OR      (@Parameter = 3 AND Column3 = 8)

However, just because you can do something, does not mean you should. Less verbose SQL does not mean better performance, so using something like:

IF @Parameter = 1
    BEGIN
        SELECT  *
        FROM    [Query]
        WHERE   Column1 = 8
    END
ELSE IF @Parameter = 2
    BEGIN
        SELECT  *
        FROM    [Query]
        WHERE   Column2 = 8
    END
ELSE IF @Parameter = 3
    BEGIN
        SELECT  *
        FROM    [Query]
        WHERE   Column3 = 8
    END

while equavalent to the first query should result in better perfomance as it will be optimised better.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s