Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

SQLServer and if/case in where clause

Have a stored proc for displaying customer info which appears as:
create proc custinfo @nonzero smallint as
select * from customer
where...

The parm @nonzero (1 or 0) limits the display of customer's balances.  If
@nonzero = 1 then only customers with balances <> 0 display.  Otherwise
all customers display.

My issue is how best to express the where clause to accomodate @nonzero.

Currently I have: ((@nonzero = 1 and balance <> 0) or (@nonzero = 0)).  Surely there's a
better solution.
0
hawand5
Asked:
hawand5
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your way is ok. there are alternatives (dynamic sql is the simple one, writing a different select for each case another one), all with other drawbacks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now