Delphi TADOQuery Select top N

Question

I am trying to select only the top N items in a TADOQuery, but it gives me and error when I activate the query. It seems to find the top parameter just fine, but fails to replace it when executing. If I don't limit the SQL statement with "Top N" the code works fine.

Here is the basic idea of the code.

const SQL_STR = 'SELECT TOP :cnt name from dSomeTable where done = FALSE';

var
  dbCon         : TADOConnection;
  toSolveQry    : TADOQuery;
  getCnt        : TParameter;
  names         : TField;
  threadCnt     : Integer;

begin
  threadCnt  := 3;
  dbCon := TADOConnection.Create(nil);
  ...
  dbCon.Open();

  toSolveQry := TADOQuery.Create(nil);
  toSolveQry.Connection := dbCon;
  toSolveQry.SQL.Add(SQL_STR);
  toSolveQry.ParamCheck := True;
  getCnt := toSolveQry.Parameters.ParamByName('cnt');
  getCnt.Value := threadCnt;

  toSolveQry.Active := true; //Error here

  names       := toSolveQry.FieldByName('name');
  ...
end

Answer 1

Parameters can't be used for column names in a SELECT or WHERE clause. This precludes use in TOP x as well.

Use the Format function instead:

const SQL_STR = 'SELECT TOP %d name from dSomeTable where done = FALSE';

toSolveQry.SQL.Text := Format(SQL_STR, [threadCnt]);
toSolveQry.Open;

Using the integer format specifier (%d) prevents SQL injection, as Delphi will raise an exception if you provide anything but an integer value as the argument to Format.