SQL Server stored procedure Nullable parameter

Question

Problem:
When values are provided to the following script then executed using a setup in C# like below (or in SQL Server environment) the values do not update in the database.

Stored procedure:

-- Updates the Value of any type of PropertyValue
-- (Type meaining simple Value, UnitValue, or DropDown)
CREATE PROCEDURE [dbo].[usp_UpdatePropertyValue]
    @PropertyValueID int,
    @Value varchar(max) = NULL,
    @UnitValue float = NULL,
    @UnitOfMeasureID int = NULL,
    @DropDownOptionID int = NULL
AS
BEGIN   
    -- If the Property has a @Value, Update it.
    IF @Value IS NOT NULL
    BEGIN
        UPDATE [dbo].[PropertyValue]
        SET
            Value = @Value
        WHERE
            [dbo].[PropertyValue].[ID] = @PropertyValueID
    END
    -- Else check if it has a @UnitValue & UnitOfMeasureID
    ELSE IF @UnitValue IS NOT NULL AND @UnitOfMeasureID IS NOT NULL
    BEGIN
        UPDATE [dbo].[UnitValue]
        SET
            UnitValue = @UnitValue,
            UnitOfMeasureID = @UnitOfMeasureID
        WHERE
            [dbo].[UnitValue].[PropertyValueID] = @PropertyValueID          
    END
    -- Else check if it has just a @UnitValue
    ELSE IF @UnitValue IS NOT NULL AND @UnitOfMeasureID IS NULL
    BEGIN
        UPDATE [dbo].[UnitValue]
        SET
            UnitValue = @UnitValue
        WHERE
            [dbo].[UnitValue].[PropertyValueID] = @PropertyValueID  
    END
    -- Else check if it has a @DropDownSelection to update.
    ELSE IF @DropDownOptionID IS NULL
    BEGIN
        UPDATE [dbo].[DropDownSelection]
        SET
            SelectedOptionID = @DropDownOptionID
        WHERE
            [dbo].[DropDownSelection].[PropertyValueID] = @PropertyValueID
    END
END

When I do an execution of this script, like below, it does not update any values.

Example execution:

String QueryString = "EXEC [dbo].[usp_UpdatePropertyValue] @PropertyValueID, @Value, @UnitValue, @UnitOfMeasureID, @DropDownOptionID";
SqlCommand Cmd = new SqlCommand(QueryString, this._DbConn);

Cmd.Parameters.Add(new SqlParameter("@PropertyValueID", System.Data.SqlDbType.Int));
Cmd.Parameters.Add(new SqlParameter("@Value", System.Data.SqlDbType.Int));
Cmd.Parameters.Add(new SqlParameter("@UnitValue", System.Data.SqlDbType.Int));
Cmd.Parameters.Add(new SqlParameter("@UnitOfMeasureID", System.Data.SqlDbType.Int));
Cmd.Parameters.Add(new SqlParameter("@DropDownOptionID", System.Data.SqlDbType.Int));

Cmd.Parameters["@PropertyValueID"].Value = Property.Value.ID; // 1
Cmd.Parameters["@Value"].IsNullable = true;
Cmd.Parameters["@Value"].Value = DBNull.Value;
Cmd.Parameters["@UnitValue"].IsNullable = true;
Cmd.Parameters["@UnitValue"].Value = DBNull.Value;
Cmd.Parameters["@UnitOfMeasureID"].IsNullable = true;
Cmd.Parameters["@UnitOfMeasureID"].Value = DBNull.Value;
Cmd.Parameters["@DropDownOptionID"].IsNullable = true;
Cmd.Parameters["@DropDownOptionID"].Value = 2; // Current Value in DB: 3

Details:

After running an execute (via C# code or SQL Server environment) it does not update dbo.DropDownSelection.SelectedOptionID. I'm guessing that it might be because dbo.DropDownSelection.SelectedOptionID is non-nullable and the parameter I'm using to set it is nullable (despite that when setting it shouldn't ever be null). Upon execution the return value is 0. If I run one of the Updates outside of the procedure they work perfectly, hence my suspicion that it has to do with null-able types.

Question(s):

Could this be because the parameters to the stored procedure are nullable and the fields I'm setting aren't?

If not, what could it be?

Solution 1

It looks like you're passing in Null for every argument except for PropertyValueID and DropDownOptionID, right? I don't think any of your IF statements will fire if only these two values are not-null. In short, I think you have a logic error.

Other than that, I would suggest two things...

First, instead of testing for NULL, use this kind syntax on your if statements (it's safer)...

    ELSE IF ISNULL(@UnitValue, 0) != 0 AND ISNULL(@UnitOfMeasureID, 0) = 0

Second, add a meaningful PRINT statement before each UPDATE. That way, when you run the sproc in MSSQL, you can look at the messages and see how far it's actually getting.

Solution 2

You can/should set your parameter to value to DBNull.Value;

if (variable == "")
{
     cmd.Parameters.Add("@Param", SqlDbType.VarChar, 500).Value = DBNull.Value;
}
else
{
     cmd.Parameters.Add("@Param", SqlDbType.VarChar, 500).Value = variable;
}

Or you can leave your server side set to null and not pass the param at all.

Comments

[TTeeple]

If you change those Insert and Updates to Selects, do you return data for the supplied parameters?

[Shelby115]

Yes. I updated my question, added "If I run one of the Updates outside of the procedure they work perfectly, hence my suspicion that it has to do with null-able types."

[Shelby115]

1) I clarified my question a bit. I'm not trying to do output parameter. 2) The stored-procedure says it executes and it returns 0, but the values never update. I'm not sure what you mean by "via sql block".

[T.S.]

Does your SP executes if you exexut it via sql block? In your case, since you have hardcoded Cmd.Parameters["@Value"].IsNullable = true; Cmd.Parameters["@Value"].Value = DBNull.Value;, you might as well just omit it.

[Shelby115]

Wow, I feel dumb. It should be ELSE IF @DropDownOptionID IS NOT NULL like the rest of them :/ Thank you.

[Kenny Evitt]

How is using ISNULL(... safer than ... IS NULL? It seems like your suggestion is less safe because, for one reason at least, now you have a magic value (zero) that is treated as equivalent to NULL. If you're going to use or allow null values, testing for NULL and only NULL is the safest thing to do.