title: Stored procedure doesn't allow null parameters even the parameter represent accept nulls?
body: <p>I saw many questions on stack overflow, non of them touched my own problem</p>
  
  <blockquote>
    <p>procedure or function expects parameter which was not supplied</p>
  </blockquote>
  
  <p>I created this SQL Server stored procedure:</p>
  
  <pre><code>CREATE proc [dbo].[spAddCustomer]
      @cuName varchar(50),
      @cuAddress varchar(50),
      @cuMobile varchar(50),
      @cuImage image,
      @cityId int,
      @exist int output
  AS
  BEGIN
      IF NOT EXISTS(SELECT Cu_Mobile FROM tblCustomers WHERE Cu_Mobile = @cuMobile)
      BEGIN
          INSERT INTO tblCustomers (Cu_Name, Cu_Address, Cu_Mobile, Cu_Image, City_ID)
          VALUES (@cuName, @cuAddress, @cuMobile, @cuImage, @cityId)
  
          SET @exist = 1
      END
      ELSE
          SET @exist = 0
  END
  </code></pre>
  
  <p>In my Data Access Layer I have this method that is responsible for non-query commands:</p>
  
  <pre><code>public static int ExecuteNonQuery(string query, CommandType type, params SqlParameter[] arr)
  {
      int outParam;
  
      SqlCommand cmd = new SqlCommand(query, cn);
      cmd.Parameters.AddRange(arr);
      cmd.CommandType = type;
  
      int i = cmd.ExecuteNonQuery();
  
      foreach (SqlParameter param in arr)
      {
          if (param.Direction == ParameterDirection.Output)
          {
              outParam = (int)cmd.Parameters[param.ToString()].Value;
              return outParam;
          }
      }
  
      return i;
  }
  </code></pre>
  
  <p>The method responsible for creating parameters:</p>
  
  <pre><code>public static SqlParameter CreateParameter(string name, SqlDbType type, object value, ParameterDirection pd = ParameterDirection.Input)
  {
      SqlParameter pr = new SqlParameter();
      pr.ParameterName = name;
      pr.Direction = pd;
      pr.SqlDbType = type;
      pr.SqlValue = value;
  
      return pr;
  }
  </code></pre>
  
  <p>And this method in the Business Layer, pass the arguments from the Presentation Layer</p>
  
  <pre><code>public static int spAddCustomer(string cusName, string cusAddress, string cusMobile, byte[] arrImg, int cityId)
  {
      DataAccessLayer.Open();
  
      int i = DataAccessLayer.ExecuteNonQuery("spAddCustomer", CommandType.StoredProcedure,
          DataAccessLayer.CreateParameter("@cuName", SqlDbType.VarChar, cusName),
          DataAccessLayer.CreateParameter("@cuAddress", SqlDbType.VarChar, cusAddress),
          DataAccessLayer.CreateParameter("@cuMobile", SqlDbType.VarChar, cusMobile),
          DataAccessLayer.CreateParameter("@cuImage", SqlDbType.Image, arrImg),
          DataAccessLayer.CreateParameter("@cityId", SqlDbType.Int, cityId),
          DataAccessLayer.CreateParameter("@exist", SqlDbType.Int, null, ParameterDirection.Output));
  
      DataAccessLayer.Close();
  
      return i;
  }
  </code></pre>
  
  <p>When the user Click add a new record is inserted into the table (<code>tblCustomers</code>)</p>
  
  <pre><code>private void btnAU_Click(object sender, EventArgs e)
  {
      byte[] imgArr;
  
      if (PbCustomer.Image == null)
             imgArr = null;
      else
      {
          MemoryStream ms = new MemoryStream();
          PbCustomer.Image.Save(ms, PbCustomer.Image.RawFormat);
          imgArr = ms.ToArray();
      }
  
      int cityId = int.Parse(cmbCities.SelectedValue.ToString());
  
      try
      {
          int exist = CustomerClass.spAddCustomer(txtName.Text, txtAddress.Text, txtMobile.Text, imgArr, cityId);
  
          if (exist == 1)
          {
              MessageBox.Show("A new customer has been saved");
              txtAddress.Text = txtMobile.Text = txtName.Text = "";
              PbCustomer.Image = null;
          }
          else
              MessageBox.Show("A customer with the same mobile number\nalready exists, add new number!");
      }
      catch (Exception ex)
      {
          MessageBox.Show(ex.Message);
      }
  }
  </code></pre>
  
  <p>But when I click the <code>Add</code> button (passing null image), I get this error:</p>
  
  <blockquote>
    <p>procedure or function 'spAddCustomer' expects parameter '@cuImage'
    which was not supplied</p>
  </blockquote>
  
  <p>Despite the table <code>tblCustomers</code> accept null values</p>