phppdoparametersbindvalue

PDO SQLSRV Using bindValue for SELECT TOP

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?

Solution

  • 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.