Hi:


I want to create an sp to do the block below. How does one handle to
items
I am adding to and using in the stringlist?

Thanks.

Best regards


bool __fastcall TNLDM::GenerateUSPSQualificationReportData()
{
bool Result;
IBMailingTransaction->StartTransaction();
try
{
//Delete all records from Qulification Report Table
IBMailingSQL->SQL->Clear();
IBMailingSQL->SQL->Add("DELETE FROM USPSQUALREPORT");
IBMailingSQL->ExecQuery();
}
catch(EIBError *E)
{
DisplayMsg(E->IBErrorCode, E->Message);
return false;
}
TStringList *QualReport = new TStringList(); <-------
try
{
QualReport->Clear();
IBQualReportQuery->SQL->Clear();
IBQualReportQuery->SQL->Add("Select distinct substr(ZIP,1,5) as
ZIP from NEWSLET");
IBQualReportQuery->Prepare();
IBQualReportQuery->Open();
IBQualReportQuery->First();
while (!IBQualReportQuery->Eof)
{

QualReport->Add(Trim(IBQualReportQuery->FieldByName("ZIP")->AsString));
IBQualReportQuery->Next();
}
IBQualReportQuery->Close();
IBQualReportQuery->SQL->Clear();
IBQualReportQuery->SQL->Add("Select distinct substr(ZIP,1,3) as
ZIP from NEWSLET");
IBQualReportQuery->Prepare();
IBQualReportQuery->Open();
IBQualReportQuery->First();
while (!IBQualReportQuery->Eof)
{

QualReport->Add(Trim(IBQualReportQuery->FieldByName("ZIP")->AsString));
IBQualReportQuery->Next();
}
IBQualReportQuery->Close();
Result = true;
}
catch(EIBError *E)
{
DisplayMsg(E->IBErrorCode, E->Message);
delete QualReport;
IBMailingTransaction->Rollback();
return false;
}
try
{
IBQualReportQuery->Close();
IBQualReportQuery->SQL->Clear();
IBQualReportQuery->SQL->Add("INSERT INTO USPSQUALREPORT");
IBQualReportQuery->SQL->Add("(TRAYZIP, RATES5B, RATES3B,
RATESBB)");
IBQualReportQuery->SQL->Add("values( :ZIP, :RATE5SB, :RATE3SB,
:RATEBSB)");
for (int i = 0; i <= QualReport->Count -1; i++)
{
IBQualReportCountQuery->Close();
IBQualReportCountQuery->SQL->Clear();
IBQualReportCountQuery->SQL->Add("SELECT COUNT(*) FROM
NEWSLET");
if (Trim(QualReport->Strings[i]).Length() == 5)
{
IBQualReportCountQuery->SQL->Add("WHERE SUBSTR(ZIP,1,5) =
:SUBZIP");
IBQualReportQuery->ParamByName("ZIP")->AsString =
Trim(QualReport->Strings[i]);
IBQualReportCountQuery->Close();
IBQualReportCountQuery->ParamByName("SUBZIP")->AsString =

Trim(QualReport->Strings[i]);
IBQualReportCountQuery->Open();
if (IBQualReportCountQuery->FieldByName("COUNT")->AsInteger

>= 200)

{
IBQualReportQuery->ParamByName("RATE5SB")->AsInteger =

IBQualReportCountQuery->FieldByName("COUNT")->AsInteger;
}
else
IBQualReportQuery->ParamByName("RATE5SB")->AsInteger = 0;
IBQualReportQuery->ParamByName("RATE3SB")->AsInteger = 0;
IBQualReportQuery->ParamByName("RATEBSB")->AsInteger = 0;
IBQualReportQuery->ExecSQL();
IBQualReportQuery->Close();
}
else if (Trim(QualReport->Strings[i]).Length() == 3)
{
IBQualReportCountQuery->SQL->Add("WHERE SUBSTR(ZIP,1,3) =
:SUBZIP");
IBQualReportQuery->ParamByName("ZIP")->AsString =
Trim(QualReport->Strings[i]);
IBQualReportCountQuery->ParamByName("SUBZIP")->AsString =

Trim(QualReport->Strings[i]);
IBQualReportCountQuery->Open();
if (IBQualReportCountQuery->FieldByName("COUNT")->AsInteger

>= 200)

{
IBQualReportQuery->ParamByName("RATE3SB")->AsInteger =

IBQualReportCountQuery->FieldByName("COUNT")->AsInteger;
IBQualReportQuery->ParamByName("RATEBSB")->AsInteger =
0;
}
else
{
IBQualReportQuery->ParamByName("RATE3SB")->AsInteger =
0;
IBQualReportQuery->ParamByName("RATEBSB")->AsInteger =

IBQualReportCountQuery->FieldByName("COUNT")->AsInteger;
}
IBQualReportQuery->ParamByName("RATE5SB")->AsInteger = 0;
IBQualReportQuery->ExecSQL();
IBQualReportQuery->Close();
}
}
}
catch(EIBError *E)
{
DisplayMsg(E->IBErrorCode, E->Message);
delete QualReport;
IBMailingTransaction->Rollback();
return false;
}

delete QualReport;
IBMailingTransaction->Commit();
return Result;
}