Q:

WHERE clause on or off depending on variable

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. 

A:

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.