TSQL: Conditional WHERE clause

There are different ways of programming conditional WHERE clause.

Method 1
========

Dynamic sql
------------

declare @select varchar(7000)
declare @where varchar(7000)
declare @i int

set @select = "select * from mytable"
set @i = 1

if @i = 1 then
    set @where = " where mycol = 1 "
else
    set @where = " where mycol = 2 "

set @select = @select + @where
exec(@select)

Method 2
========

if else conditions
------------------

if @i = 1
    select * from mytable where mycol = 1
else
    select * from mytable where mycol = 2

Method 3
==========

Case instruction
-----------------

select * from mytable
where
mycol =  case when @i = 1 then 1 else 2 end

You have to make choice pending your situation but I tend to stay away from
dynamic sql as much as possible and try to use better techniques such as case
instruction (in my opinion).

Some time ago I ran into the following situation where I had to choose between the
above 3 approaches and I was able to solve it using case statement. Here is the
scenario. Lets say you have @custid and it could be matched to primary customer id
pcustid or secondary customer id scustid depending upon status parameter
@statusid in table called mytable. So if @statusid is 1 then primary matching
else secondary matching.

Our input parameters are declared as follows

declare @custid int
declare @statusid int

Here are the use cases

1. When none of the params are initialized then return all the rows.
2. When only custid is passed then return all the rows for that customer.
3. When @custid is passed along with the @statusid then only the related row
would show up.

Dynamic sql way is very easy to implement so I will not address that.
Lets look at the if then else statement.

    if (@custid is not null)
    begin
        if (@statusid = 1)
            select
                *
            from mytable t
            where t.pcustid  = @custid

        else if (@statusid= 2)
            select
                *
            from mytable t
            where t.scustid = @custid
        else
            select
                *
            from mytable t
            where
            (t.pcustid = coalesce( @custid,t.pcustid) )
            or
            (t.scustid = coalesce( @custid, t.scustid) )

    end
    else
            select
                *
            from mytable

This could be implemented as follws in terms of case statement.

    select * from mytable t
    where coalesce(t.pcustid,0) = (
                        case when (@custid is not null) then
                            case when (@pcustid = 1) then
                                @custid
                            else coalesce(t.pcustid,0)
                            end
                        else
                            coalesce(t.pcustid,0)
                        end
        )
        and coalesce(t.scustid,0) = (
                        case when (@custid is not null) then
                            case when (@pcustid = 2) then
                                @custid
                            else coalesce(t.scustid,0)
                            end
                        else
                            coalesce(t.scustid,0)
                        end
        )
        and (
            (coalesce(t.pcustid,0) = coalesce( @custid, coalesce(t.pcustid,0)) )
            or
            (coalesce(t.scustid,0)= coalesce( @custid, coalesce(t.scustid,0)) )
        )

Coaslesce is heaven sent function. In case pcustid column is null, I will not
be able to do exact match with it self if all else fails to render it as
TRUE result. So the following match would return true

coalesce(t.pcustid,0)  = coalesce(t.pcustid,0)

Otherwise null column would not result as equal

t.pcustid = t.pcustid does not return true if column is null.

So see if you could revisit your dynamic sql statements and see if you
could REFACTOR them into nice CASE driven where clause.

Thanks

ZULFIQAR SYED

Comments

[test]

You could do the entire thing using the IsNull function instead of coalesce

[Lukas Holota]

thanks, exacktly the idea I was looking for, you saved my ass :)

[Abhishek]

Thanks man...Using Case is an excellent option.

[qwkmz fgxwsv]

ofiev rqgpswcft kupjhtqwa ldsknzf gmpzjbo wyvz eftsjhpuv

[rg]

What about:

...
and (t.pcustid = @custid or @custid = -1)
...

If you pass -1 to @custid, all customers would be returned.