CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2008-r2
Shelby115
Shelby115

Reputation: 2865

SQL Server stored procedure Nullable parameter

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?

Upvotes: 32

Views: 213877

Answers (2)

user1040975
user1040975

Reputation: 440

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.

Upvotes: 5

Ash8087
Ash8087

Reputation: 701

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.

Upvotes: 17

Related Questions