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

2 Answers2

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,740
  • 3
  • 40
  • 50
1

I know this question has been answered already but I was able to solve this problem quite easily and I want to help future visitors to this question with an alternate solution.

The solution I used was to add parenthesis around :rowsLimit so that the SQL looks like:

SELECT TOP (:rowsLimit) * FROM TABLE ORDER BY id DESC

You must also cast your parameter to an int if is not already otherwise you will get a datatype sql exception

here is an example from my code that works!

$runNumber = (int)$_POST["runNumber"];
$numberOfRecords = (int)$_POST["numberOfRecords"];

$conn = new PDO(/*connection string details*/);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result = $conn->prepare("SELECT TOP (?) * FROM CustomerInfo where Run = ? order by ControlNumber");
$result->setFetchMode(PDO::FETCH_ASSOC);
$result->bindParam(1, $numberOfRecords, PDO::PARAM_INT);
$result->bindParam(2, $runNumber, PDO::PARAM_INT);
$result->execute();

Hopefully this can help someone else who has a similar issue.

Kotorfreak
  • 11
  • 6