i have a stored proc that does a simple update (see below).

i want to update only fields passed and not the ones that are null. that is to say if i dont get a parm value for somethign i dont want to update the table data to null, but rather just leave what the current value is.

can this be done in a sql update statement such as this?

ALTER PROCEDURE [dbo].[upd_MessageDetail]  
    @MessageId INT,  
    @IsDraft INT,  
    @IsPreviewed INT,  
    @IsRead INT,  
    @IsFlagged INT,  
    @IsDeleted INT,  
    @IsArchived INT    

        UPDATE MessageDetail   
            IsDraft = @IsDraft,  
            IsPreviewed = @IsPreviewed,  
            IsRead = @IsRead,  
            IsFlagged = @IsFlagged,  
            IsDeleted = @IsDeleted,  
            IsArchived = @IsArchived   
        WHERE MessageId = @MessageId
  • This is outside the scope of the original question, but @isDraft, @isRead, etc. seem to be something that would be better represented by BIT values rather than INT values.
    – Jeff
    Jun 17, 2011 at 17:38

2 Answers 2


Something like below if I understand you correctly.

UPDATE MessageDetail   
            IsDraft = ISNULL (@IsDraft, IsDraft), 
            IsPreviewed = ISNULL (@IsPreviewed,  IsPreviewed),
            IsRead = ISNULL (@IsRead, IsRead), 
            IsFlagged = ISNULL (@IsFlagged, IsFlagged), 
            IsDeleted = ISNULL(@IsDeleted, IsDeleted)
            IsArchived = ISNULL(@IsArchived, IsArchived)
        WHERE MessageId = @MessageId
  • 2
    The above is ok and doesn't incur lot of overhead as long as you don't have any Non-clustered indexes on these columns. If there are they you are making an additional IO as well. Otherwise you can use dynamic sql or convoluted lot of if else statements here. May 25, 2011 at 15:36
  • I think a convoluted lot of if else statements is the only way for super performant, but looking at the example, I think this is more than sufficient.
    – jcolebrand
    May 25, 2011 at 16:37

An addendum to Sankar Reddy's answer is that if you are using Replication or using triggers that check if a column has been updated (using the Update() function) then it might be worthwhile creating a convoluted IF .. ELSE statement to not include the field in the UPDATE statement.

specifically from the MSDN article

Detection of real column updates
UPDATE() detects whether a column is part of an UPDATE (INSERT) statement. It does not detect whether a value was changed by that statement. This also applies to the COLUMNS_UPDATED() function.


  1. Convoluted IF .. ELSE statement
    Check all possible update combinations (so that you only do one update call to the DB with minimal network bandwidth)
    • good: reduces data sent over the wire/to the table (useful for highly replicated tables)
    • good: you can use UPDATE() function to check if value has changed...
    • bad: exponential complexity as number of columns on tables increases (ie: 2 column table requires 3 checks, are both columns being updated? is column 1 updated? is column 2 updated? adding 3rd column increases it to 8 checks)
  2. Simplified IF .. ELSE statement
    Allow multiple update calls by having update of each column individually...
    • good: simplified sql in stored proc (easier to maintain/less error prone)
    • bad: updates are not atomic
    • bad: increased IO traffic as more calls to db
  3. String parsing
    Parse up the update statement in a big string and then exec it
    • good: Simplified IF .. ELSE combined with 1 update call on the DB
    • bad: Potential security risk from SQL injection (can be minimised by using parameters)
    • bad: slight CPU overhead as sql statement is parsed rather than compiled (though I think that isn't any worse than the other 2 options above)


  1. Sankar Reddy's answer - it's the simplest to code/maintain and is well understood
  2. If performance (IO/network) is really an issue then do the string parsing as a last resort (there are easier performance gains than this!)...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.