| Author |
 Topic  |
|
|
sillybus
Starting Member
USA
13 Posts |
 Posted - 12/06/2002 : 10:34:30
|
I am using the following delete statement inside a sproc.
DELETE FROM myTable
WHERE
my_year = @year AND
my_catID = @catID AND
my_subCatID = COALESCE(@subCatID,my_subCatID) AND
my_detailCatID = COALESCE(@detailCatID,my_detailCatID)
The situation is this:
1. There will always be a non null cat ID as a param and as a value in the table.
2. There may exist a top-level record where sub category may be null both as a param and as a table entry.
3. There may exist a record where the sub cat is not null but the detail cat is null.
If I pass in the values 2001,1,1,1 (where param1 is year, param2 is cat, param3 is subcat and param4 is detail cat) then the appropriate record deletes fine, If I pass in 2001,1,1,NULL which is looking for a record where the detail cat in the database is actually NULL then it doesn't work (Because you are trying to see if a column = NULL, not IS NULL)
Am I just not thinking clearly this morning or what? This should be simple but I just can't see it
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 12/06/2002 : 10:43:22
|
DELETE FROM myTable
WHERE my_year = @year AND my_catID = @catID AND
(my_subCatID IS NULL OR my_subCatID = COALESCE(@subCatID,my_subCatID)) AND
(my_detailCatID IS NULL OR my_detailCatID = COALESCE(@detailCatID,my_detailCatID))
|
 |
|
|
sillybus
Starting Member
USA
13 Posts |
Posted - 12/06/2002 : 10:45:54
|
Rob, you are a genious... guess thats what i get for skipping Dunkin Donuts on the way into work... no brain fuel. LOL
|
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 12/06/2002 : 11:06:07
|
mmmmmmmmmmmmmmmmmmmmmmmmmmmm, Dunkin Donuts 
|
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 12/06/2002 : 11:24:04
|
I think the main problem with the COALESCE function is that it's too hard to spell ....
- Jeff |
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
Posted - 12/06/2002 : 11:29:06
|
Not as hard as INFORMATION_SCHEMA is to type. The number of times I've typed INFROMATION...
|
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 12/06/2002 : 11:35:53
|
Hey, I screw up FROM (FORM) quite a bit 
SELECT * FORM INFROMATION_SHCEMA.COLUMNS --I've typed this more times than I'll ever admit to
|
|
|
| |
Topic |
|
Dynamic Where (w/ COALESCE) & Source Col IS NULL
