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