sql server 2008 - How to write a stored procedure in ASP.NET -

the stored procedure there below need added in code 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 callcenter..loy_dispmstr calltype=@calltype , 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  loy_dispmstr calltype=@calltype , subformat=@format , 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 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(); } 

stored procedure:

if @flag = '1' begin select formatid,formatdetail,dispformat  loy_formatdetail (nolock) isactive='1' , memberstatus = 'member' order formatdetail end  if @flag = '2' begin select distinct disposition callcenter..loy_dispmstr  calltype=@calltype , subformat=@format end  if @flag = '3'   begin   select distinct calltype,disposition,subdisposition,format  loy_dispmstr calltype=@calltype , subformat=@format , disposition = @disposition end 

in first code paragraph:

cmd.commandtype = commandtype.storedprocedure; cmd.commandtext = "sp_whatevername"; cmd.parameters.addwithvalue("@calltype", ddlcalltype.selectedvalue); cmd.parameters.addwithvalue("@format", ddlformat.selecteditem.text); cmd.parameters.addwithvalue("@flag", "1"); 

in ddldisp_selectedindexchanged:

using (sqlcommand cmd = new sqlcommand("sp_whatevername", con)) {       cmd.commandtype = commandtype.storedprocedure;        cmd.parameters.addwithvalue("@calltype",ddlcalltype.selectedvalue);       cmd.parameters.addwithvalue("@format", ddlformat.selectedvalue);       cmd.parameters.addwithvalue("@disposition", ddldisp.selectedvalue);       cmd.parameters.addwithvalue("@flag", "1");        con.open();       cmd.executenonquery();     } 

i have updated code parameter flag. sp should have parameters , specific condition need pass specific flag. in way can result flag specific query.

hope helps.