1

I'm trying to use PDO SQLSRV to select data from a table with a limit (TOP). However, when I do this.

$limit = 20;

$sql = "SELECT TOP :rowsLimit * FROM TABLE ORDER BY id DESC";
$query = $this->db->prepare($sql);
$parameters = array(':rowsLimit' => $limit);

$query->execute($parameters);

I get an error like this.

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 102 [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '@P1'.

I even tried removing the paremeters and adding a bindValue instead, but the same error occurs even with either of these.

$query->bindValue(':rowsLimit', (int) trim($limit), PDO::PARAM_INT);

or

$query->bindValue(':rowsLimit', intval(trim($limit)), PDO::PARAM_INT);

So how can I bind a parameter for the TOP in PDO SQLSRV?

Wokki
  • 147
  • 2
  • 18
  • 1
    Table and Column names cannot be replaced by parameters in PDO. Check this [link](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter) for more detail – Saty Jul 18 '15 at 09:43
  • @Saty I see. Thank you. – Wokki Jul 18 '15 at 09:51

1 Answers1

3

You can't use parameters for the TOP value, but there is a workaround for that issue.

What you need to do is use ROW_NUMBER() OVER() syntax and manually filter out the top @x rows.

See the sqlfiddle for an example.

Anonymous
  • 11,142
  • 3
  • 39
  • 49