title: Multiple queries is single stored procedure
body: <p>I am trying to use a single stored procedure for INSERT, UPDATE, DELETE and SELECT statements.
  Separately they work fine but when combined I get the following error on all the above queries.</p>
  
  <p>Procedure or function expects parameter which was not supplied.</p>
  
  <p>Here is the procedure.</p>
  
  <pre><code>USE [XXX]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[spCmpStructure]
  
      @action varchar(7),
      @cmpID varchar(10),
      @cmpName varchar(50), 
      @cmpDesc varchar(100),
      @cmpAddress varchar(50),
      @cmpType varchar(20),
      @cmpParent varchar(50)
  
  AS
  BEGIN
  
  SET NOCOUNT ON; 
  
  --INSERT Record
  if @action = 'INSERT'
  BEGIN
  INSERT INTO cmpStructure(cmpID, cmpName, cmpDesc, cmpAddress, cmpType, cmpParent)
                          VALUES
                          (@cmpID, @cmpName, @cmpDesc, @cmpAddress, @cmpType, @cmpParent)
  END 
  
  --UPDATE record
  else if @action = 'UPDATE'
  BEGIN
  UPDATE cmpStructure SET cmpName=@cmpName, cmpDesc=@cmpDesc, cmpAddress=@cmpAddress,      cmpType=@cmpType, cmpParent=@cmpParent WHERE cmpID=@cmpID
  END
  
  --DELETE Record
  else if @action = 'DELETE'
  BEGIN
  DELETE FROM cmpStructure WHERE cmpID=@cmpID
  END
  
  --SELECT/SEARCH Record
  else if @action = 'SELECT'
  BEGIN
  SELECT * FROM cmpStructure
  END
  
  END
  </code></pre>
  
  <p>And here is the c# code</p>
  
  <pre><code> // Add Record
      private void btnAdd_Click(object sender, EventArgs e)
      {
          try
          {
              SqlCommand commandp = new SqlCommand("spCmpStructure", dbcon.con);
              commandp.CommandType = CommandType.StoredProcedure;
  
              commandp.Parameters.AddWithValue("@action", ins);
              commandp.Parameters.AddWithValue("@cmpID", txtID.Text.ToString());
              commandp.Parameters.AddWithValue("@cmpName", txtCmp.Text.ToString());
              commandp.Parameters.AddWithValue("@cmpDesc", txtDetails.Text.ToString());
              commandp.Parameters.AddWithValue("@cmpAddress", txtAddress.Text.ToString());
              commandp.Parameters.AddWithValue("@cmpType", cbType.Text.ToString());
              commandp.Parameters.AddWithValue("@cmpParent", cbParent.Text.ToString());
  
              dbcon.openConnection();
  
              commandp.ExecuteNonQuery();
  
              dbcon.closeConnection();
          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message);
          }
          finally
          {
              dbcon.closeConnection();
          }
  
      }
   // Update Record
      private void btnUpdate_Click(object sender, EventArgs e)
      {
          try
          {
              SqlCommand commandp = new SqlCommand("spCmpStructure", dbcon.con);
              commandp.CommandType = CommandType.StoredProcedure;
  
              commandp.Parameters.AddWithValue("@action", upd);
              commandp.Parameters.AddWithValue("@cmpID", txtID.Text.ToString());
              commandp.Parameters.AddWithValue("@cmpName", txtCmp.Text.ToString());
              commandp.Parameters.AddWithValue("@cmpDesc", txtDetails.Text.ToString());
              commandp.Parameters.AddWithValue("@cmpAddress", txtAddress.Text.ToString());
              commandp.Parameters.AddWithValue("@cmpType", cbType.Text.ToString());
              commandp.Parameters.AddWithValue("@cmpParent", cbParent.Text.ToString());
  
              dbcon.openConnection();
  
              int checkUpdate = commandp.ExecuteNonQuery();
  
              if (checkUpdate &gt; 0)
              {
                  MessageBox.Show(txtCmp.Text.ToString() + " Updated");
              }
              else
              {
                  MessageBox.Show(txtCmp.Text.ToString() + " Update Failed");
              }
  
              dbcon.closeConnection();
          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message);
          }
          finally 
          {
              dbcon.closeConnection();
          }
      }
   // Remove Record
      private void btnRemove_Click(object sender, EventArgs e)
      {
          try
          {
              SqlCommand commandr = new SqlCommand("spCmpStructure", dbcon.con);
              commandr.CommandType = CommandType.StoredProcedure;
  
              commandr.Parameters.AddWithValue("@action", del);
              commandr.Parameters.AddWithValue("@cmpID", txtID.Text.ToString());
  
              dbcon.openConnection();
  
              int checkRemoved = commandr.ExecuteNonQuery();
  
              if (checkRemoved &gt; 0)
              {
                  MessageBox.Show(txtCmp.Text.ToString() + " Removed");
              }
              else
              {
                  MessageBox.Show(txtCmp.Text.ToString() + " Removal Failed");
              }
  
              dbcon.closeConnection();
          }
          catch(Exception ex)
          {
              MessageBox.Show(ex.Message);
          }
          finally
          {
              dbcon.closeConnection();
          }
      }
  
      private void companyStructure_Load(object sender, EventArgs e)
      {
          try
          {
              using (SqlCommand commandg = new SqlCommand("spCmpStructure", dbcon.con))
              {
                  commandg.CommandType = CommandType.StoredProcedure;
  
                  commandg.Parameters.AddWithValue("@action", sel);
  
                  dbcon.openConnection();
  
                  SqlDataReader dr = commandg.ExecuteReader();
                  DataTable dt = new DataTable();
                  dt.Load(dr);
                  dgvCompany.DataSource = dt; 
  
                  dbcon.closeConnection();
              }
  
          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message);
          }
          finally
          {
              dbcon.closeConnection();
          }
  
          try
          {
              using (SqlCommand commands = new SqlCommand("spCmpStructure", dbcon.con))
              {
                  commands.CommandType = CommandType.StoredProcedure;
  
                  commands.Parameters.AddWithValue("@action", sel);
  
                  dbcon.openConnection();
  
                  SqlDataReader dr = commands.ExecuteReader();
  
                  while (dr.Read())
                  {
                      cbType.Items.Add(dr["cmpType"].ToString());
                      cbParent.Items.Add(dr["cmpParent"].ToString());
                  }
                  dbcon.closeConnection();
              }
          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message);
          }
          finally
          {
              dbcon.closeConnection();
          }
  
      }
  </code></pre>