Saturday, November 20, 2010

A Smart Overloading of a Stored Procedure Call by Mining COALesce.

Often you have a table where different columns needs to be updated by different calls.  Traditionally, you wrote a stored procedure for every variation which can result in a mountain of code (increases your LOC/hr but drives maintenance cost up more!)

 

IF you can eliminate NULL as being a valid value (use ‘’ instead?) then you can reduce everything to a single stored procedure as illustrated below.

 

Create proc harvest.P_UpsertEvent @ID uniqueidentifier=null ,@ParentID uniqueidentifier=null ,@ConsolidationSequenceNo bigint=null ,@Level smallint=null ,@EventFulId varchar(50)=null ,@VenueID uniqueidentifier=null ,@Title nvarchar(120)=null ,@Text nvarchar(max)=null ,@Html nvarchar(max)=null ,@StartAt datetime=null ,@EndAt datetime=null ,@ExtendedXml xml=null AS If Exists(Select 1 from Events.Event Where ID=@ID) BEGIN Update [Events].[Event] SET [ConsolidationSequenceNo]=Coalesce(@ConsolidationSequenceNo,ConsolidationSequenceNo) ,[Level]=Coalesce(@Level,Level) ,[EventFulId]=Coalesce( @EventFulId,EventFulId) ,[VenueID]=Coalesce(@VenueID,VenueID) ,[Title]=Coalesce(@Title,Title) ,[Text]=Coalesce(@Text,Text) ,[Html]=Coalesce(@Html,Html) ,[StartAt]=Coalesce(@StartAt,StartAt) ,[EndAt]=Coalesce(@EndAt,EndAt) ,[ExtendedXml]=Coalesce(@ExtendedXml,ExtendedXml) Where ID=@ID END

In short, if a new (NOT NULL) value is supplied, it is used – otherwise the existing value is used.

 

If you are working with column level permissions, then you can just insert logic to set values from an unauthorized user (for the column but not the row) to a null. This actually makes the number of lines of code to implement column level permissions a fraction of other implementations.

3 comments:

  1. While convenient, you need to be aware of performance issues that might crop up. If any of the columns being updated are foreign keys to another table, changing (even to the same value) them will cause SQL Server to enforce the referential-integrity rules. This can cause an index lookup on the target table.

    Thus, ONLY do this trick when you aren't touching FK values or don't care about performance.

    ReplyDelete