在SQL查询中多次使用相同的参数

Question

我正在使用PYODBC和Microsoft SQL Server，

我正在尝试在Python中复制一个存储过程，在该过程中，对于

SELECT *
                FROM
                (
                    SELECT
                        SurveyId,
                        QuestionId,
                        1 as InSurvey
                    FROM
                        SurveyStructure
                    WHERE
                        SurveyId = @currentSurveyId
                    UNION
                    SELECT 
                        @currentSurveyId as SurveyId,
                        Q.QuestionId,
                        0 as InSurvey
                    FROM
                        Question as Q
                    WHERE NOT EXISTS
                    (
                        SELECT *
                        FROM SurveyStructure as S
                        WHERE S.SurveyId = @currentSurveyId AND S.QuestionId = Q.QuestionId
                    )
                ) as t
                ORDER BY QuestionId

Python中的每个@CurrentsUrveyId执行此查询，我到目前为止都可以使用：

cursor.execute("""SELECT UserId FROM dbo.[User]""")
allSurveyID = cursor.fetchall()

for i in allSurveyID:
    p = i
    test = cursor.execute("""SELECT *
                FROM
                (
                    SELECT
                        SurveyId,
                        QuestionId,
                        1 as InSurvey
                    FROM
                        SurveyStructure
                    WHERE
                        SurveyId = (?)
                    UNION
                    SELECT 
                        (?) as SurveyId,
                        Q.QuestionId,
                        0 as InSurvey
                    FROM
                        Question as Q
                    WHERE NOT EXISTS
                    (
                        SELECT *
                        FROM SurveyStructure as S
                        WHERE S.SurveyId = (?)AND S.QuestionId = Q.QuestionId
                    )
                ) as t
                ORDER BY QuestionId""",p)

for i in test:
    print(i)

该参数可在使用时使用（如果我从Union of Union wardswards中删除所有内容） 。在查询的其余部分中尝试使用相同的参数时，我会收到以下错误:('SQL包含3个参数标记，但提供了1个参数'，'hy000'）

是否可以多次使用相同的参数在同一查询中？

谢谢

Answer 1

PYODBC本身仅支持“ Qmark”（位置）参数（ref：），但是对于T-SQL（Microsoft SQL Server），我们可以使用匿名代码块避免多次传递相同的参数值：

cnxn = pyodbc.connect(connection_string)
crsr = cnxn.cursor()

sql = """\
SET NOCOUNT ON;
DECLARE @my_param int = ?;
SELECT @my_param AS original, @my_param * 2 AS doubled;
"""
results = crsr.execute(sql, 2).fetchone()
print(results)  # (2, 4)

Answer 2

如果重复使用相同的参数值，只需乘以参数的一个单项列表：

cursor.execute(sql, [p]*3) 

还请考虑左JOIN（或完整加入）需要两个Qmarks：

SELECT DISTINCT
       ISNULL(S.SurveyId, ?) AS SurveyId, 
       Q.QuestionId, 
       IIF(S.SurveyId IS NOT NULL, 1, 0) AS InSurvey 
FROM Question Q
LEFT JOIN SurveyStructure S
  ON S.QuestionId = Q.QuestionId 
  AND S.SurveyId = ?
ORDER BY Q.QuestionId

可能是可能的 ：即使是一个参数：

SELECT MAX(S.SurveyId) AS SurveyId, 
       Q.QuestionId, 
       IIF(S.SurveyId IS NOT NULL, 1, 0) AS InSurvey 
FROM Question Q
LEFT JOIN SurveyStructure S
  ON S.QuestionId = Q.QuestionId 
  AND S.SurveyId = ?
GROUP BY Q.QuestionId, 
         IIF(S.SurveyId IS NOT NULL, 1, 0)
ORDER BY Q.QuestionId