Dynamic Where (w/ COALESCE) & Source Col IS NULL

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Where (w/ COALESCE) & Source Col IS NULL

Author

Topic

sillybus
Starting Member

13 Posts

Posted - 2002-12-06 : 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

15732 Posts

Posted - 2002-12-06 : 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))