在 tsql 和 pyodbc 中参数化 TOP 值 [英] Parametrizing TOP value in tsql and pyodbc

问题描述

我尝试参数化要从表中获取的顶行数.

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

我用

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

它显示了

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC 驱动程序 17对于 SQL Server][SQL Server]'@P1' 附近的语法不正确.(102)(SQLExecDirectW)")

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?

推荐答案

您可以通过用括号将值括起来来参数化 top:

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;

应用于您发布的代码:

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

这篇关于在 tsql 和 pyodbc 中参数化 TOP 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！