Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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    
AS  
BEGIN  


        UPDATE MessageDetail   
        SET 
            IsDraft = @IsDraft,  
            IsPreviewed = @IsPreviewed,  
            IsRead = @IsRead,  
            IsFlagged = @IsFlagged,  
            IsDeleted = @IsDeleted,  
            IsArchived = @IsArchived   
        WHERE MessageId = @MessageId
share|improve this question
    
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 '11 at 17:38

2 Answers 2

up vote 8 down vote accepted

Something like below if I understand you correctly.

UPDATE MessageDetail   
        SET 
            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
share|improve this answer
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. –  Sankar Reddy May 25 '11 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 '11 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.

Options:

  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)

Recommendation

  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!)...
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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