Welcome to weblogs.com.pk Sign in | Join | Help

If-Else Like Behavior in Where Clause

The other day I was reviewing a database and in its one sproc; the developer had a query something like

if @OnlyConfirmed = 1

 select invoice-columns
 from invoices
 where IsConfirmed = 1
  and username = @UserName

else

 select invoice-columns
 from invoices
 where username = @UserName

 

The more simpler alternate is that you use If-Else like behavior in Where Clause; something like

select invoice-columns
from invoices
where username = @UserName
 and isconfirmed = case when @OnlyConfirmed <> 1 then isconfirmed else @OnlyConfirmed end

or its Boolean Algebraically deductive form (which SQL Server will like)

select invoice-columns
from invoices
where username = @UserName
 and (
  @OnlyConfirmed <> 1 or isconfirmed = @OnlyConfirmed
 )
Published Monday, September 28, 2009 6:57 PM by khurram
Filed under: ,

Comments

No Comments

New Comments to this post are disabled