This might be something of an atypical post for this blog in that it's a solicitation of feedback rather than a "sermonette," but so be it. Perhaps this is the start of a positive trend.. :-)
I was looking over this code from a previous post:
UPDATE sSET SupplementTitle = CASE NULLIF(i.SupplementTitle, d.SupplementTitle) WHEN NULL THEN s.SupplementTitle ELSE dbo.fnStripLowAscii(s.SupplementTitle) END, FileAttachmentPath = CASE NULLIF(i.FileAttachmentPath, d.FileAttachmentPath) WHEN NULL THEN s.FileAttachmentPath ELSE dbo.fnStripLowAscii(s.FileAttachmentPath) END, MachineDataReference = CASE NULLIF(i.MachineDataReference, d.MachineDataReference) WHEN NULL THEN s.MachineDataReference ELSE dbo.fnStripLowAscii(s.MachineDataReference) ENDFROM inserted iJOIN dbo.Supplement sON i.SupplementId = s.SupplementIdLEFT OUTER JOIN deleted dON i.SupplementId = d.SupplementId
..and I was struck by the insight that there had to be a better, more compact way to code this block. After a little tweaking, I found what I was looking for:
I've convinced myself that the two syntaxes are functionally equivalent, both in terms of results and performance.
I've also convinced myself that this is a scenario where brevity is not a virtue. I find the previous syntax to be far more maintainable.
If you were handed this function "cold" to support, which syntax would you prefer? Are there other scenarios where verbosity is a virtue?
Your feedback would be of great interest..
I like the first one better. Cleaner and easier to read to me.
Any chance you could abstract the CASE statement logic into a User Defined Function? Sometimes that helps make the code easier to maintain. It has been awhile since I have done any udf, so i'm not sure if it is an option or not. I wrote an entry a couple of years ago that showed one at: http://jasonhaley.com/blog/archive/2004/03/03/8319.aspx
Interesting idea, Jason.. only practical in SQL Server 2005, since we're accessing the inserted and deleted trigger views.. I will look into that idea.