Cannot use variables in sql queries except in where clause

[Dilhasha]

Description:

int batchSize = 70;
.....
sql:ParameterizedQuery sqlQuery = `SELECT TOP ${batchSize} guid , date FROM MigrationRecords`

This gives an error as follows.

Incorrect syntax near '@P0'.. {"errorCode":102,"sqlState":"S0001"}

When the batchSize is hard coded in the query, this works fine. If the variable is in where clause, it doesn't give any error and picks up the value.

Steps to reproduce:

Affected Versions:

OS, DB, other environment details and versions:

Related Issues (optional):

Suggested Labels (optional):

Suggested Assignees (optional):

[Dilhasha]

Workaround:
Use OFFSET 0 ROWS FETCH FIRST ${batchSize} ROWS ONLY; instead of TOP ${batchSize}

[niveathika]

This is the expected behaviour as top clause value cannot be parameterized i.e it cannot be a user insertions at runtime.

Database expects the top clause value at compile time and this needs to be available in the query.