Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
 Posted - 2009-03-09 : 18:10:07
|
GreetingsI have table ShiftMinutesShiftMinuteID intCommodityID intDefinitionValue intDefinitionDate datetimeBranchID intUserDate datetimeUserName varchar DefinitionValue is the sum minutes for a branch, commodity. Sometime thought a shift could handle any kind of commodity and decided to make it a nullable field. Now when I have @CommodityID as NULL paramater and do the following in WHERE clause on ShiftMinutes table WHERE CommodityID = @CommodityID I get no result. But when I do WHERE (CommodityID = @CommodityID OR commodityid IS NULL) I get the result I want. But is this a good design or did I just stumble on a solution, which I don't like I want to know why it worked and why it did not.Thanks !! |
|
|
guptam
Posting Yak Master
161 Posts |
Posted - 2009-03-09 : 18:30:13
|
| You cannot compare NULL to a field. That is why you have to use commodityis IS null. NULL is a special value and cannot be compared. Your solution is fine..-- Mohit K. GuptaB.Sc. CS, Minor JapaneseMCITP: Database AdministratorMCTS: SQL Server 2005http://sqllearnings.blogspot.com/ |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-10 : 03:10:17
|
quote:
Originally posted by guptam
You cannot compare NULL to a field. That is why you have to use commodityis IS null. NULL is a special value and cannot be compared. Your solution is fine..-- Mohit K. GuptaB.Sc. CS, Minor JapaneseMCITP: Database AdministratorMCTS: SQL Server 2005http://sqllearnings.blogspot.com/
You can compare if you set ANSI NULL SETTING to off. then NULL will be regarded as a value too |
|
|
|
|
|
|
when column has NULL value