title: SQL Server stored procedure Nullable parameter
body: <p><strong>Problem</strong>:<br>
  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.</p>
  
  <p>Stored procedure:</p>
  
  <pre><code>-- 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 &amp; 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
  </code></pre>
  
  <p>When I do an execution of this script, like below, it does not update any values.</p>
  
  <p>Example execution:</p>
  
  <pre class="lang-cs prettyprint-override"><code>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
  </code></pre>
  
  <p><strong>Details:</strong></p>
  
  <p>After running an execute (via C# code or SQL Server environment) it does not update <code>dbo.DropDownSelection.SelectedOptionID</code>. I'm guessing that it might be because <code>dbo.DropDownSelection.SelectedOptionID</code> 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.</p>
  
  <p><strong>Question(s):</strong></p>
  
  <p>Could this be because the parameters to the stored procedure are nullable and the fields I'm setting aren't?</p>
  
  <p>If not, what could it be?</p>