ASP.NET - How to loop through multiple variable using while loop

Asked By bhanupratap singh on 20-Mar-13 01:28 AM
How to loop through multiple variable using while loop. I am passing multiple variable one time in single parameter from code behind of c# Asp.net to stored procedure. I want to use while loop inside stored procedure on multiple value

String listbbtoo;
                StringBuilder ListBoxFromValue = new StringBuilder();
                 StringBuilder ListBoxTooValue = new StringBuilder();
                string Fromvalue, Toovalue;
                for (int i = 0; i < ListBoxFrom.Items.Count; i++)
                {
                    if (ListBoxFrom.Items[i].Selected)
                    {
                        string Listbfrom = ListBoxFrom.Items[i].Text;
                        ListBoxFromValue.Append(Listbfrom);
                        ListBoxFromValue.Append(",");
                        
                    }
                  
                }
                Fromvalue = ListBoxFromValue.ToString();
                Fromvalue = Fromvalue.Substring(0, Fromvalue.Length - 1);
                       


                        for (int j = 0; j < ListBoxToo.Items.Count; j++)
                        {
                            if (ListBoxToo.Items[j].Selected)
                            {
                                listbbtoo = ListBoxToo.Items[j].Text;
                                ListBoxTooValue.Append(listbbtoo);
                                ListBoxTooValue.Append(",");
                        
                             }
                  
                          }
                            Toovalue = ListBoxTooValue.ToString();
                            Toovalue = Toovalue.Substring(0, Fromvalue.Length - 1);
                       
            
                                con = new SqlConnection(Constr);
                                con.Open();
                                //SqlCommand CMDBOK = new SqlCommand("sp_SelectForBookingBaseCheckingWhileloop", con);
                                SqlCommand CMDBOK = new SqlCommand("sp_SelectForBookingBase", con);
                                CMDBOK.CommandType = CommandType.StoredProcedure;
                                CMDBOK.Parameters.Add("@CODE_NAME", SqlDbType.Char).Value = Fromvalue.ToString();
                                CMDBOK.Parameters.Add("@TOO", SqlDbType.Char).Value = Toovalue.ToString();
                                CMDBOK.Parameters.Add("@sdate", SqlDbType.Date).Value = txtStartDate.Text;
                                CMDBOK.Parameters.Add("@edate", SqlDbType.Date).Value = txtEndDate.Text;


                                SqlDataAdapter dap = new SqlDataAdapter(CMDBOK);
                                dap.Fill(dsBooking, "AllRecords");
                                dsForBinding.Merge(dsBooking);
                                ViewState["sssss"] = dTable;
                                con.Close();
                                //dataSet.Merge(t2);
                     
                                ViewState["MYquery"] = dTable.ToString();


                                GrvBooking.DataSource = dsForBinding;
                                GrvBooking.DataBind();


                    }
             
            }
       

Procedure need to first validate data with database table data then it will stored in #tmp table looping through all records of table matching multiple value passed in single parameter . at last the result which are stored int #tmp table is need to be selected and bind to gridview.
ALTER procedure  [dbo].[sp_SelectForBookingBaseCheckingWhileloop]
(
@CODE_NAME varchar(40)    
,@TOO varchar(40)    
,@sdate DATE
,@edate DATE


)
as
begin
  select  CODE_NAME AS BOOKING_BRANCH, count(CODE_NAME) AS TOTCN, sum(FREIGHT) AS FREIGHT,SUM(RC)AS RC,
SUM(CARTRAGE)AS CARGE,SUM(OSC)AS OSC,SUM(HAMALI) AS HAMALI,SUM(OTHER)AS OTHER,SUM(INSURANCE) AS INS,
SUM(BILTYCHARGE) AS BC, SUM(PF)AS PF,SUM(CLAIM)AS CLAIM, sum(TOTAL) AS TOTAL, SUM(STAX) AS TAX,
sum(GTOTAL)AS GTOTAL, 
ISNULL(SUM(GTOTAL+ADVANCE+BALANCE),0) as TBBTOPAYPAID INTO #tmpMatches
from Consignment
where CODE_NAME=@CODE_NAME and TOO=@TOO and (CNDT BETWEEN @sdate AND @edate) group by CODE_NAME
   end
how to do it.
thanks