Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

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

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

  • Comments 2
  • Likes

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

    Thanks!

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment