Parametrizing TOP value in tsql and pyodbc

问题:

I try to parametrize number of top rows to get from table.

I tried it with

db.cursor.execute(
        '''
        SELECT TOP ? VALUE FROM mytable 
        WHERE param = ? 
        ''',
        top_value, param
    )

and it showed

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW)")

with string interpolation like bellow it works.

    db.cursor.execute(
        f'''
        SELECT TOP {top_limit} VALUE FROM mytable 
        WHERE SITE_SK_FK = ? 
        ''',
        param
    )

Do I need to pass it as parameter, or string interpolation is good enough?

回答1:

You can parameterize top by surrounding the value with parenthesis:

DECLARE @Top int = 5;

With Tally(N) AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY @@SPID)
    FROM sys.objects
)

-- This works just fine
SELECT TOP (@Top) N
FROM Tally;

-- This will raise an error: Incorrect syntax near '@Top'
SELECT TOP @Top N 
FROM Tally;

Applied to the code you've posted:

SELECT TOP (?) VALUE 
FROM mytable 
WHERE param = ? 

回答2:

You can use string formatting for the TOP (and a proper parameter for the WHERE) provided that top_limit is an int so there is very little danger of SQL Injection issues.