CODEWITHSUNDEEP
sqlsql-server-2005
oJM86o
oJM86o

Reputation: 2118

Update of column happens even though I don't want it to happen

I've got a case statement:

UPDATE
   Answer
SET
   AnswerID = @AnswerID,
   AnsweredBy = CASE WHEN LEN(@AnsweredBy) > 0 THEN @AnsweredBy END

Even when @AnsweredBy is NULL it still sets the AnsweredBy column to null.

I've tried to test for null as well by doing this:

 UPDATE
    Answer
 SET
    AnswerID = @AnswerID,
    AnsweredBy = CASE WHEN @AnsweredBy IS NOT NULL THEN @AnsweredBy END

Meaning I do not want to update the answeredby column unless there is a value.

Even in my C# code I do not pass a value to my stored procedure:

if (answeredBy.Length > 0)
   cmdSelect.Parameters.Add("@AnsweredBy", SqlDbType.VarChar).Value = answeredBy;

unless there is a value. And in my sproc I default that column variable to null:

@RunoffAnswerID bigint,
@AnswerID varchar(3)=NULL,
@AnsweredBy varchar(50)=NULL,

So my question is how do I perform the rest of my updates, because there are about 5-10 more columns but only update the answeredby if there is a value in @AnsweredBy?

Upvotes: 0

Views: 115

Answers (2)

podiluska
podiluska

Reputation: 51514

UPDATE 
    Answer 
SET 
    AnswerID = @AnswerID, 
    AnsweredBy = CASE WHEN LEN(@AnsweredBy) > 0 THEN @AnsweredBy ELSE AnsweredBy END

Upvotes: 2

Sean
Sean

Reputation: 15182

SET AnsweredBy = ISNULL(@AnsweredBy, AnsweredBy)

Upvotes: 6

Related Questions