how to write store procedure in asp.net : [enter image description here][1]


> the stored procedure which is there below need to be added in this code where i have written query


 if (ddlFormat.SelectedIndex != 0)
            {

                String strConnString = ConfigurationManager.ConnectionStrings["CallcenterConnectionString"].ConnectionString;

                SqlConnection con1 = new SqlConnection(strConnString);


                con1.Open();
                SqlCommand cmd = new SqlCommand();
                SqlDataAdapter sda = new SqlDataAdapter();
                DataSet dsDisp = new DataSet();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "Select DISTINCT Disposition from CallCenter..Loy_DispMstr where CallType=@CallType and SUBFormat=@Format";
                cmd.Parameters.AddWithValue("@CallType", ddlCalltype.SelectedValue);
                cmd.Parameters.AddWithValue("@Format", ddlFormat.SelectedItem.Text);
                cmd.Connection = con1;
                cmd.ExecuteNonQuery();

                sda.SelectCommand = cmd;
                sda.Fill(dsDisp);


                ddlDisp.DataTextField = "Disposition";
                ddlDisp.DataValueField = "Disposition";

                ddlDisp.DataSource = dsDisp.Tables[0];
                ddlDisp.DataBind();

                ddlDisp.Items.Insert(0, "<----Select---->");
                ddlDisp.Focus();
            }
        }
        

  

        protected void ddlDisp_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddlDisp.SelectedIndex != 0)
            {
                String strConnString = ConfigurationManager.ConnectionStrings["CallcenterConnectionString"].ConnectionString;
            
                SqlConnection con = new SqlConnection(strConnString);
                SqlCommand cmd=new SqlCommand();
                SqlDataAdapter sda = new SqlDataAdapter();
                DataSet dsSubDisp = new DataSet();
             
                using (cmd = new SqlCommand("Select distinct CallType,Disposition,SubDisposition,Format from  Loy_DispMstr where CallType=@CallType and SUBFormat=@Format and Disposition = @disposition", con))
                {
                    cmd.Parameters.AddWithValue("@CallType",ddlCalltype.SelectedValue);
                    cmd.Parameters.AddWithValue("@Format", ddlFormat.SelectedValue);
                    cmd.Parameters.AddWithValue("@disposition", ddlDisp.SelectedValue);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    
                }
                sda.SelectCommand = cmd;
                sda.Fill(dsSubDisp);
                   {
                        
                        ddlSubdisp.DataTextField = "SubDisposition";
                        ddlSubdisp.DataValueField = "SubDisposition";
                        ddlSubdisp.DataSource = dsSubDisp.Tables[0];
                        ddlSubdisp.DataBind();

                        ddlSubdisp.Items.Insert(0, "<----Select---->");
                        ddlSubdisp.SelectedIndex = 0;
                        ddlSubdisp.Focus();

                        ddlDisp.Items.Insert(1, "ADD NEW VALUE");
                        ddlDisp.SelectedIndex = 1;
                        ddlDisp.Focus();



                    }

               }
                if (ddlDisp.SelectedItem.Text == "ADD NEW VALUE" )
                       {
                            TextBox1.Visible = true;
                            TextBox2.Visible = true;
                       }
            }


        protected void ddlSubdisp_SelectedIndexChanged(object sender, EventArgs e)
        {
            String strConnString = ConfigurationManager.ConnectionStrings["CallcenterConnectionString"].ConnectionString;

            SqlConnection con = new SqlConnection(strConnString);
            
            SqlDataAdapter sda = new SqlDataAdapter();
            DataSet dsOut = new DataSet();

            SqlCommand cmd = new SqlCommand("select PID,Memberstatus,calltype,format,disposition,subdisposition, man_data,creation_date,createdby,updation_date,updatedby from Loy_SubPlaceholder");
                cmd.Connection = con; 
                sda.SelectCommand = cmd;
                sda.Fill(dsOut);
                ddlDisp.DataSource = dsOut.Tables[0];
                ddlDisp.DataValueField = "subdisposition";
                ddlDisp.DataTextField = "subdisposition";
                ddlDisp.DataBind();
                con.Open();

                cmd.ExecuteNonQuery();
              
      

 - store procedure
if @flag = '1'
begin
Select Formatid,Formatdetail,dispformat  From loy_Formatdetail with (nolock)
Where isactive='1' and memberstatus = 'Member' order by FormatDetail
end

if @flag = '2'
begin
Select DISTINCT Disposition from CallCenter..Loy_DispMstr 
where CallType=@CallType and SUBFormat=@Format
end

if @flag = '3'
begin
Select distinct CallType,Disposition,SubDisposition,Format from  Loy_DispMstr
where CallType=@CallType and SUBFormat=@Format and Disposition = @disposition
end



        