Title: In SQL clause with more than 255 elements.
Question: How to create IN SQL clause with more than 255 elements.
Answer:
It should be adjusted to your needs, but here it is the code:
function GetInClause(strField: string): string;
var
strInClause: string;
intCounter1: integer;
blnAtLeastOneFoundGenerally: boolean;
blnAtLeastOneFound: boolean;
intRecords: integer;
begin
// Returns a IN clause from a client dataset, divided in pairs of 255.
strInClause := '';
intCounter1 := 0;
blnAtLeastOneFound := false;
blnAtLeastOneFoundGenerally := false;
strInClause := strField + ' IN (';
for intRecords := 0 to dbgrdInsured.SelectedRows.Count - 1 do
begin
inc(intCounter1);
qryInsured.GotoBookmark(pointer(dbgrdInsured.SelectedRows.Items[intRecords]));
// Handle more that 255 items in IN clause.
if (intCounter1 = 255) and blnAtLeastOneFound then
begin
Delete(strInClause, Length(strInClause), 1);
strInClause := strInClause + ')';
strInClause := strInClause + ' AND ' + strField + ' IN (';
intCounter1 := 0;
end;
blnAtLeastOneFound := true;
blnAtLeastOneFoundGenerally := true;
strInClause := strInClause + qryInsured.FieldByName('ID_EMP').AsString + ',';
end;
if blnAtLeastOneFound then
begin
Delete(strInClause, Length(strInClause), 1);
strInClause := strInClause + ')';
end;
if blnAtLeastOneFoundGenerally then
Result := strInClause
else
Result := '';
end;