通过

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

  • Anonymous
    March 20, 2006
    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

  • Anonymous
    March 21, 2006
    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!