Database Programming: Feedback Requested — Which Syntax Is More Maintainable?


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  s
SET     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)
       
END
FROM
    inserted i
JOIN    dbo.Supplement s
ON      i.SupplementId = s.SupplementId
LEFT OUTER JOIN
      
 deleted d
ON      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:


UPDATE s
SET    SupplementTitle = ISNULL(NULLIF(i.SupplementTitle,ISNULL(NULLIF (i.SupplementTitle,d.SupplementTitle),s.SupplementTitle)),dbo.fnStripLowAscii(s.SupplementTitle
)),
       FileAttachmentPath = ISNULL(NULLIF(i.FileAttachmentPath,ISNULL(NULLIF (i.FileAttachmentPath,d.FileAttachmentPath),s.FileAttachmentPath)),dbo.fnStripLowAscii(s.FileAttachmentPath
)),
       MachineDataReference = ISNULL(NULLIF(i.MachineDataReference,ISNULL(NULLIF (i.MachineDataReference,d.MachineDataReference),s.MachineDataReference)),dbo.fnStripLowAscii(s.MachineDataReference
))
FROM
   inserted i
JOIN   dbo.
Supplement s
ON     i.SupplementId = s.
SupplementId
LEFT OUTER
JOIN
       deleted d
ON     i.SupplementId = d.SupplementId


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..


   -wp

Comments (2)

  1. Jason Haley says:

    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

  2. Ward Pond says:

    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.

    Thanks!