I created a test type as a table with the below columns:
CREATE TYPE [dbo].[TestType] AS TABLE
(
[TestField] [varchar](10) NULL,
[TestField2] [int] NULL
)
I then created a stored procedure the takes that table type as a parameter.
CREATE PROCEDURE TestTypeProcedure (@tt TestType READONLY)
AS
SELECT *
FROM @tt;
My goal is to be able to pass something like a list of lists as the parameter for the table type. Is that even possible?
myList = [['Hello!', 1], ['Goodbye!', 2]]
....
cursor.execute('{{Call {TestTypeProcedure} ({?})}}', myList)
pyodbc.ProgrammingError: ('The SQL contains 1 parameter markers, but 2 parameters were supplied', 'HY000')
You are getting that error because a table-valued parameter is a list of iterables (preferably tuples) ...
my_tvp = [('Hello!', 1), ('Goodbye!', 2)]
print(f"my_tvp contains {len(my_tvp)} row(s)")
# my_tvp contains 2 row(s)
... and if you pass that directly to .execute() then each row is interpreted as a parameter value:
sql = "{CALL TestTypeProcedure (?)}"
params = my_tvp
print(f"calling SP with {len(params)} parameter value(s)")
# calling SP with 2 parameter value(s)
crsr.execute(sql, params) # error
Therefore, you need to wrap your tvp inside a tuple to make it a single parameter value
sql = "{CALL TestTypeProcedure (?)}"
params = (my_tvp, ) # tuple containing a single tvp "object"
print(f"calling SP with {len(params)} parameter value(s)")
# calling SP with 1 parameter value(s)
crsr.execute(sql, params) # no error
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments