Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 905
Shelby115 Avatar asked Feb 20 '14 20:02

Shelby115


People also ask

How will you define optional parameter in SQL stored procedure?

A parameter is considered optional if the parameter has a default value specified when it is declared. It is not necessary to provide a value for an optional parameter in a procedure call. The default value of a parameter is used when: No value for the parameter is specified in the procedure call.

Is null in stored procedure SQL Server?

Inside the stored procedure, the parameter value is first tested for Null using the ISNULL function and then checked whether it is Blank (Empty). If the parameter has value then only matching records will be returned, while if the parameter is Null or Blank (Empty) then all records from the table will be returned.


2 Answers

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.

like image 114
Ash8087 Avatar answered Sep 28 '22 08:09

Ash8087


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.

like image 34
user1040975 Avatar answered Sep 28 '22 08:09

user1040975