CELKO (12/14/2012)
.. Total failure.. Besides which, I believe my earlier post was the same idea, except coded correctly 🙂 .
Yeah, it was awful! :crazy:
What I wanted to do was avoid the CASE expression solution and go for something using functions that compile to simple assembly language.
DELETE FROM Client_Shares
WHERE COALESCE (SIGN(ABS(lm_id)+1), 0)
+ COALESCE (SIGN(ABS(ml_id) +1), 0)
+ COALESCE (SIGN(ABS(is_id) +1), 0)
+ COALESCE (SIGN(ABS(t_id)+1), 0) >= 2;
Why overcomplicate it? Why not the solution I posted, that simply uses Where <col> Is Null? Your solution doesn't improve anything on this, makes it impossible for SQL Server to use indexes on the columns, makes it unclear to anyone reading the code what it's supposed to do (you'll need to add a lot of documentation to your version), and seems to be complexity for the sake of "cleverness".
Edit: And, so far as I know, "Where <col> Is Null" is valid in every version of SQL I've ever seen, so your solution isn't even "more portable".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon