I am using pyodbc and Microsoft SQL Server

I am trying to replicate a stored procedure in python where this query is executed for every @currentSurveyId

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

In Python, I so far have:

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)

The parameter works when used once (if I delete everything from UNION onwards). When trying to use the same parameter in the rest of the query, I get the following error:('The SQL contains 3 parameter markers, but 1 parameters were supplied', 'HY000')

Is it possible to use the same parameter multiple times in the same query?

Thank you

CodePudding user response：

pyodbc itself only supports "qmark" (positional) parameters (ref: here), but with T-SQL (Microsoft SQL Server) we can use an anonymous code block to avoid having to pass the same parameter value multiple times:

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)

CodePudding user response：

If reusing the same parameter value, simply multiply a one-item list of the parameter:

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

Consider also refactoring your SQL for LEFT JOIN (or FULL JOIN) requiring two 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

Possibly even for one parameter:

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