CODEWITHSUNDEEP
sqlsql-serversqlparameter
Terramune
Terramune

Reputation: 31

SQL Where logic parameterized where

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

Upvotes: 1

Views: 34

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

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

Upvotes: 3

Related Questions