CODEWITHSUNDEEP
sql-server-2008stored-proceduresbit
Keith Myers
Keith Myers

Reputation: 1369

SQL Server 2008 sp with nullable bit arg: how to avoid an if/else for select?

How can I avoid the if/else please? The 'Active' column can not be null in the table.

CREATE   procedure spExample(@bitActive bit)
as

if @bitActive is not null
    select      a.PKID 
    from        Alerts a 
    where       a.Active = @bitActive
else
    select      a.PKID 
    from        Alerts a

Upvotes: 0

Views: 249

Answers (2)

DeanOC
DeanOC

Reputation: 7282

Or, you could go with

select      a.PKID 
from        Alerts a 
where       ISNULL(@bitActive, a.Active) = a.Active

This is essentially the same as the preceding answer, but avoids using an OR in the Where.

I should point out that you may well get better performance using your current code, as it avoids executing the IS NULL test on every row. If you are using indexes on your data, the IS NULL test may mean that your database engine's query optimiser cannot use the indexes which may produce a sub-optimal execution path.

I can tell you from experience that we have seen significant improvement (mins to seconds) by removing the ISNULL checks and writing SQL that is specific to the parameters either by using IF statements or dynamic SQL.

We are using Sql Server 2008, so your mileage may vary if you use another engine. I suggest you do some tests to see if there are any performance issues on your database.

Cheers, Dean.

Upvotes: 3

Magnus
Magnus

Reputation: 46947

select      a.PKID 
from        Alerts a 
where       @bitActive IS NULL OR a.Active = @bitActive

Upvotes: 1

Related Questions