WHERE clause on or off depending on variable

Question

Hi I am using SQL server 2008 R2. Would like to do the following:

declare
    -- only one of these @ vars will be used
    @specificAccountNumber int = NULL
    @specificAccountNumber int = 123456


select
    account
    ,accountinfo1
    ,accountinfo2
from tableA
where
    if @specificAccountNumber is NULL
    then use the whole table
    else /* @specificaAccountNumber is not null */
    account = @specificAccountNumber


select
    account
    ,accountinfo3
    ,accountinfo4
from tableB
where
    if @specificAccountNumber is NULL
    then use the whole table
    else /* @specificAccountNumber is not null */
    account = @specificAccountNumber

The goal is for the queries to select all rows if I don't speicify an account number (or set @specificAccountNumber to NULL), but if I do specify then it only brings data for that account.

Answer 1

Sending different queries with different where clauses in the application would be the best way to handle this.

However, if it must be done in the database, I think COALESCE will work for this particular case. Try this:

WHERE account = COALESCE(@specificAccountNumber, account)

COALESCE chooses the first non-NULL entry in its input list. What this should do is compare the account column with itself if @specificAccountNumber is NULL. I'm not sure if this will hurt the performance when @specificAccountNumber is specified. If tableA will be large in production, you should check the query plan.

Answer 2

You can use a case statement in where clause something like this

declare
    -- only one of these @ vars will be used
    @specificAccountNumber int = NULL
    @specificAccountNumber int = 123456


select
    account
    ,accountinfo1
    ,accountinfo2
from tableA
where account = CASE 
                    WHEN  @specificAccountNumber IS NULL THEN account 
                    ELSE @specificAccountNumber END