SQL Where logic parameterized where

Question

I'm attempting to create a parameterized where statement using COALESCE in SQL 2008, and I have the example of:

Select * 
From Receipts 
Where Receipts.FunctionCode = COALESCE(@FunCode, Receipts.FunctionCode)

in hope that if i pass in NULL in @FunCode, it will pull all 7050 records. However its only pulling back 236 records, and same as if the where was this: Where Receipts.FunctionCode = Receipts.FunctionCode

Can someone explain what logic is wrong for me? To me, this where statement should always pull 100% back the database

Well, are there values where Receipts.FunctionCode are NULL? — Lamak
– Lamak, Commented Mar 30, 2015 at 19:00

Pரதீப் · Accepted Answer · 2015-03-30 19:00:22Z

3

That is due to the NULL values present in the FunctionCode column try this instead. This will use index created on FunctionCode if any

Select * 
From Receipts 
Where Receipts.FunctionCode = @FunCode or @FunCode IS NULL

answered Mar 30, 2015 at 19:00

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Where logic parameterized where

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related