CODEWITHSUNDEEP
c#sql-server
Arno Nym
Arno Nym

Reputation: 49

Stored procedure called from code behaves different than in SQL Management Studio with NULL values

This is the stored procedure

CREATE PROCEDURE [Proc_DeleteSpecificRow]     
 @p_Id int
,@p_subId   int
,@p_col1 varchar(50) = NULL
,@p_col2 varchar(50) = NULL
,@p_col3 varchar(50) = NULL
AS        
BEGIN   
if(@p_col2 is not null AND @p_col3 is not null)
  Delete from TestTable where Id= @p_Id AND SubId= @p_subId
  AND col2= @p_col2 AND col3= @p_col3

When I run this stored procedure with following values in SQL Server management studio, it works perfectly:

The Row with above values gets removed. The same happens, if i leave col2 and col3 empty, and just provide a value for col1. It works as intended.

Now if I call this stored procedure with equivalent data/values from c# code, it does not work. There is no exception, it just does nothing. My guess is, that it has something to do with the NULL values. This is how I pass the parameters to the IDbCommand:

IMPORTANT: It has NOTHING to do with the stuff around the call itself, other procedures work fine.

EDIT (Code):

    [DataAccessorMethod(SupportedInterfacesFlags = AccessorInterfaces.IDataModifier)]
    public void DeleteSpecificRow(int id, int subId, string col1, string col2, string col3, string connectionString)
    {
      try
      {
        using (var scope = new TransactionScope(TransactionScopeOption.Required, Constants.TxOptions))
        {
          using (IDbConnection conn = new SqlConnection(connectionString))
          {
            IDbCommand cmd = MyHelper.GetCommand(conn, Constants.PROC_DELETESPECIFICROW, CommandType.StoredProcedure);
            MySqlHelper.AddParameter(cmd, "p_Id", DbType.Int16, id);
            MySqlHelper.AddParameter(cmd, "p_SubId" ,DbType.Int16, subId);
            MySqlHelper.AddParameter(cmd, "p_col1", DbType.String, col1);
            MySqlHelper.AddParameter(cmd, "p_col2", DbType.String, col2);
            MySqlHelper.AddParameter(cmd, "p_col3", DbType.String, col3);
            conn.Open();
            cmd.ExecuteNonQuery();
            scope.Complete();
          }
        }
      }
      catch (SqlException sqlex)
      {
      ...}
    }

Upvotes: 0

Views: 168

Answers (3)

TriV
TriV

Reputation: 5148

I think the problem is you didn't send null value param to stored procedure. You could use DBNull.Value to pass null value like this

 MySqlHelper.AddParameter(cmd, "p_col1", DbType.String, 
                           string.IsNullOrEmpty(col1)? DBNull.Value : col1);
 MySqlHelper.AddParameter(cmd, "p_col2", DbType.String, 
                           string.IsNullOrEmpty(col2) ? DBNull.Value : col2);
 MySqlHelper.AddParameter(cmd, "p_col3", DbType.String, 
                           string.IsNullOrEmpty(col3) ? DBNull.Value : col3);

Another solution is change your stored procedure to

if(isnull(@p_col2,'') != '' AND isnull(@p_col3,'') != '')
begin
     -- do stuff
end

Upvotes: 0

Mehmet Topçu
Mehmet Topçu

Reputation: 1364

When you call stored procedure from c sharp , you can see from SQL Server Profiler , is statement true or false.

Upvotes: 1

Dominik Mayrhofer
Dominik Mayrhofer

Reputation: 491

The Problem with null values could be the check in the where clause if a parameter is null because e.g. col2 = null would not work. Instead you could try something like this AND (@p_col2 IS NULL OR @p_col = col2) for all columns. Another way would be a dirty hack like ISNULL(@p_col, 999) = ISNULL(col2, 999)

Upvotes: 0

Related Questions