Stored Procedure parameter inserting wrong value

Question

Good afternoon everyone,

I am having an issue with a stored procedure inserting an incorrect value. Below is a summarization of my stored procedure ...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[InsertDifferential]
    @differential int = null
AS
    BEGIN TRY
        BEGIN TRANSACTION
      UPDATE
       DifferentialTable
      SET
       differential = @differential
        COMMIT
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        ROLLBACK
        DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int, @ErrorState INT
        SELECT @ErrMsg = ERROR_MESSAGE(), 
         @ErrSeverity = ERROR_SEVERITY(),
         @ErrorState = ERROR_STATE();
        RAISERROR(@ErrMsg, @ErrSeverity, @ErrorState);
    END CATCH

Below is the code I use to call the stored procedure ...

SqlConnection dbEngine = new SqlConnection(connectionString);
SqlCommand dbCmd = new SqlCommand("InsertDifferential", dbEngine);
SqlDataAdapter dataAdapter = new SqlDataAdapter(dbCmd);

dbCmd.CommandType = CommandType.StoredProcedure;

if (myobject.differential.HasValue)
{ dbCmd.Parameters.AddWithValue("@differential", myobject.differential); }
else
{ dbCmd.Parameters.AddWithValue("@differential", DBNull.Value); }

dbCmd.ExecuteNonQuery();

In the database table, the differential column is a nullable int with no default value.

The differential property of "myobject" is an int? data type set to null by default.

The issue is when I run the stored procedure, the differential column winds up with a 0 in place. Even if "myobject.differential" is null and I pass in the DBNull.Value the column still winds up with a 0 in place. I've tried not passing the @differential into the stored procedure and it still sets the column to 0.

I've tried a number of different solutions and nothing has worked.

Thank you in advance for any assistance,

Scott Vercuski

Answer 1

I believe that when you set the default value on a parameter like

@differential int = null

You do not need to add it to your SQL Command.

Try the code with just this and do not include the else...

if (myobject.differential.HasValue)
{ 
   dbCmd.Parameters.AddWithValue("@differential", myobject.differential); 
}

Answer 2

Are you certian that the differential column does not have a Default Value Constraint on it of zero?

Answer 3

ALso check to see if there is a trigger on the table setting null values to zero.