Case in Where with Null
-
I have a stored procedure that takes two parameters. A select is executed that must find records matching the two parameters unless the second is null then it should find rows where that column is null. Can this be done in one statement without using dynamic sql.
Here's the select that won't work because of the "product_id = " and NUll or Is null in the where.
Select *
From mytable
Where unit_id = @unit_id
And product_id =
Case
When @product_id is NOT null then @product_Id
When @product_id is null then IS NULL -- HERE is the problem
End
Thanks, ron
-
What happens when you remove the IS from:
IS NULL -- HERE is the problem
?
SJT
-
I still don't think it would work because null doesn't equal null so that part of the query wouldn't work...
how about :
where product_id is null and @ProductId is null or
@product_id = product_id
-
Select *
From mytable
Where unit_id = @unit_id
And IsNull(product_id, -1) = IsNull(@product_Id, -1)
Replace -1 with the correct datatype for the ID and if -1 is a valid ID in your domain, replace the hard-coded constant with a value that would never arise in real data.
[Edit] Remi's solution will work fine, I still superstitiously use the IsNull() method due to having used Sql Server since version 4.x and not trusting the optimizer to do the right thing when it sees an "Or".
-
Thanks for all the quick replies. I think PW's solution is the one that fits the bill since in my case when @product_id is null I want to exclude any records where product_id is other than null.
-
Taking a second closer look (as PW mentioned), Remi's solution should also work just fine.
Thanks again to all.
-
SET ANSI_NULLS OFF
Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<>
comparison operators when used with null values.Try this in northiwind table then
set ansi_nulls off
go
SELECT * FROM employees where reportsto = null
go
set ansi_nulls on
go
SELECT * FROM employees where reportsto = null
This may will help you.
Greetings,
-
Pls allow me to explain my self better.
If you use [ set ansi_nulls off ] you will be able to use null as value comparison this means:
column1 = null is equals to column1 is null.
Now why this is important? and what is the difference from PW's solution?. Well this answer is "indexes". Again lets use Northwind database.
Create this index:
CREATE INDEX INX_ReportsTo on Employees(reportsTo)
After that using the SQL Query Analyzer, go the menu Query and click the option "Show Execution Plan",
Then execute this queries:
set ansi_nulls off
go
SELECT * FROM employees where reportsto = null
go
set ansi_nulls on
go
SELECT * FROM employees where isNull(reportsto,-1) = isNull(NULL,-1)
Take a look at the tab that Says "Execution Plan" it will show a difference in the execution, being the first query more efficient because of the index we created.
-
Personal preference: don't like to play with set options unless I have to... especially when someone else may be changing the sp later on.
I had considered PW's option of ISNULL(, -1) but I had chosen not to use it because I thaught SQL couldn't use the index. Also I thaught that if the parameter wasn't null, then the query governor would simply ignore the IS NULL condition because it could never be true. I did some testing on a table of my own and it turns out I was almost right :
Declare @FkParentOBJ as int
SET @FkParentOBJ = NULL
--using top so that the number of lines returned is the same
Select TOP 10 * from dbo.ObjSQL WHERE FkParentOBJ IS NULL AND @FkParentOBJ IS NULL
OR FkParentOBJ = @FkParentOBJ
--index seek (10%) + bookmark lookup(90%) (27.37% of the lot)
--CPU : 0, READS : 81, DURATION : 0
GO
Declare @FkParentOBJ as int
SET @FkParentOBJ = 254925
Select * from dbo.ObjSQL WHERE FkParentOBJ IS NULL AND @FkParentOBJ IS NULL
OR FkParentOBJ = @FkParentOBJ
--index seek (10%) + bookmark lookup(90%) (27.37% of the lot)
--CPU : 0, READS : 81, DURATION : 0
GO
Declare @FkParentOBJ as int
SET @FkParentOBJ = 254925
Select * from dbo.ObjSQL WHERE ISNULL(FkParentOBJ, -1) = ISNULL(@FkParentOBJ, -1)
--clustered index scan (94%) + select (6%) (45.26% of the lot)
--CPU : 13, READS : 125, DURATION : 16
GO
The test table only has 6.5k rows but it already shows a 50% decrease in performance using the isnull() method. I would assume that on a large table, this would translate in a lot of I/Os especially if the unused index is very selective (unlike mine which is very far from a unique index).
-
- Remi what you are saying is not totally wrong, actually I consider it a good aproach but, I have done some tests of my own and your solution also cannot take advantange of the indexes.
- Your solution is faster when it has a value different from null but if you use "null" the performace of your solution is equals to PW's solution.
If you understant set options, you will see much of them does not have a side effect or are just there for "emergency cases". Set ansi_null off, is still the best choice on performance and scallability because it is taking all the advantages of indexes.
By the way I just do this for fun and having a nice discussion, my apologies if I am hurting somebodies feelings... I hope you enjoy it as much as I do....
Greetings,
-
I guess that my test case is wrong because when I run this :
set ansi_nulls off
Declare @FkParentOBJ as int
SET @FkParentOBJ = null
Select top 10 * from dbo.ObjSQL WHERE FkParentOBJ = @FkParentOBJ
set ansi_nulls on
I get the exact same execution plan than I get from the first 2 queries (with composite where condition).
Do you have a test case from Northwind that can show the index difference (not that I don't believe you.. but I'd like to understand why there's no difference in this case)?
I guess I'm relunctant to use set options because everytime I used 'em was because I was forced too.. not by choice. Time will tell if that changes
. -
Sure, here is the example I am using to test:
CREATE INDEX INX_ReportsTo on Employees(reportsTo)
go
set ansi_nulls off
go
SELECT * FROM employees where reportsto = null
go
set ansi_nulls on
go
SELECT * FROM employees where isNull(reportsto,-1) = isNull(NULL,-1)
go
Declare @reportsTo INT
set @reportsTo = 5
SELECT * FROM employees where (reportsto is Null AND @reportsTo is Null) OR reportsTo = @reportsTo
- And I am using the "execution plan" to test them.
Pls review it maybe I could be doing something wrong
.Regards,
-
Plan 1 and 3 are extactly the same. Obviously the isnull() plan takes 60% of the load which is expected.
R u getting different execution plan for the first and last query?
if yes : what version of sql server do you have (got 2000 SP3a)
-
My apologies you are right! I think I might get a dirty read and I didn't notice. Plan 1 and 3 are the same.
I am sorry, when I checked Plan 2 and 3 looked exactly the same but I think is because I was doing something wrong.
Pls forgive me it really was not my intention...
-
np... I could have been wrong too... or it could have been simply the selectivity of the index that changed the whole thing. At least we both learned something from this and it may help somebody else someday.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply