SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Dynamic Where (w/ COALESCE) & Source Col IS NULL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sillybus
Starting Member

USA
13 Posts

Posted - 12/06/2002 :  10:34:30  Show Profile  Send sillybus an AOL message  Reply with Quote
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
15676 Posts

Posted - 12/06/2002 :  10:43:22  Show Profile  Visit robvolk's Homepage  Reply with Quote
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))

Go to Top of Page

sillybus
Starting Member

USA
13 Posts

Posted - 12/06/2002 :  10:45:54  Show Profile  Send sillybus an AOL message  Reply with Quote
Rob, you are a genious... guess thats what i get for skipping Dunkin Donuts on the way into work... no brain fuel. LOL

Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 12/06/2002 :  11:06:07  Show Profile  Visit robvolk's Homepage  Reply with Quote
mmmmmmmmmmmmmmmmmmmmmmmmmmmm, Dunkin Donuts

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 12/06/2002 :  11:24:04  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
I think the main problem with the COALESCE function is that it's too hard to spell ....


- Jeff
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 12/06/2002 :  11:29:06  Show Profile  Reply with Quote
Not as hard as INFORMATION_SCHEMA is to type. The number of times I've typed INFROMATION...


Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 12/06/2002 :  11:35:53  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000