Record in table is not updated when another record is null

Question

I already posted the similar question by with different interpretation.

I'm looking for the solution to the following problem:

I have a stored procedure that is called from my code.

I need to update a record in my table and set it to either 1 or 0. It is a bit datatype.

My stored procedure accepts 3 parameters: region, isActive and Number:

This is my stored procedure:

ALTER PROCEDURE [dbo].[SPU_UpdateEmai] 

@region char(2),
@isoNum varchar(10),
@isActive bit

AS
BEGIN
    SET NOCOUNT ON;

    UPDATE MyTable
    SET isActive = @isActive, updatedate = GETDATE()    
    WHERE region = @region AND isonumber = @isoNum

END

When @isoNumber is not empty, then I can update my isActive field, if @isoNumber is empty, nothing happens.

When simply executing update:

    UPDATE ActivateEmailSendToIso
    SET isActive = 0, updatedate = GETDATE()    
    WHERE region = '04' AND isonumber is null

everything is fine. But when running the code, update does not happen. This is the code:

    using (SqlCommand cmd = new SqlCommand("SP_UpdateEmail", conn))
    {
        conn.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@region", actData.Region);
        //cmd.Parameters.AddWithValue("@isoNum", actData.IsoNumber);
        cmd.Parameters.AddWithValue("@isoNum", (actData.Equals(String.Empty)) ? (object)DBNull.Value : actData.IsoNumber);
        cmd.Parameters.AddWithValue("@isActive", actData.IsActive);
        cmd.ExecuteNonQuery();
        isRecordSaved = true;
    }

Everything seems to be fine.

What can possibly be wrong?

Thank you

Personally, updating `updateDate`-type columns should be done with a trigger - it prevents you from forgetting to do it somewhere. This is happening because SQL doesn't consider `null` to be equal to **anything**, including another `null`. The result of a comparison (other than `IS NULL`) is _also_ null, because the db has no way to tell what the answer should be. [See this question for more info](http://stackoverflow.com/questions/9581745/sql-is-null-and-null). Essentially, this is a duplicate question, although a very nicely asked one. — Clockwork-Muse, May 02 '14 at 00:19
Sorry, for another question, but how then I should update my stored procedure? Can you please, update my question with the right answer? — eugene.it, May 02 '14 at 01:15

score 0 · Accepted Answer · answered May 02 '14 at 01:27

If you still want to update records when @isoNum is null, then change your procedure to this:

ALTER PROCEDURE [dbo].[SPU_UpdateEmai] 

@region char(2),
@isoNum varchar(10),
@isActive bit

AS
BEGIN
    SET NOCOUNT ON;

    UPDATE MyTable
    SET isActive = @isActive, updatedate = GETDATE()    
    WHERE region = @region 
    AND (isonumber = @isoNum or @isoNum IS NULL AND isonumber IS NULL)

END

Record in table is not updated when another record is null

1 Answers1