Python call procedure with TVP parameter that returns multiple tables in SQL server

Question

I am trying to call a stored procedure with sqlalchemy to a microsoft sql server. The procedure takes a custom table valued parameter and returns back multiple tables.

The only way to send a TVP i found that is working is using this code that I found online and uses pyodbc in sqlalchemy for the connection.

data = {"tvp": [("foo",1), ("bar",2)]}
sql = f"{{CALL testtypeprocedure ( :tvp)}}"
print(conn.execute(sqlalchemy.text(sql), data).fetchall())

While it manages to send the TVP and execute the procedure it only returns to python the first table that the stored procedure selects.

one way to get back multiple tables that i found is using this type of code.

connection = engine.raw_connection()
cursor = connection.cursor()
cursor.callproc(procedure_name, [param1,param2])

cursor.nextset()
cursor.fetchall()

and this works only when I am using pymssql in sqlalchemy to make the connection, but i cant find a way to send a TVP this way.

Is there any way to do both with either pyodbc or pymssql in sqlalchemy?

A single parameter should be passed as a tuple (data,)

Charlieface
– Charlieface

2024-02-20 22:25:57 +00:00
Commented Feb 20, 2024 at 22:25 — Charlieface
– Charlieface, Commented Feb 20, 2024 at 22:25

Gord Thompson · Accepted Answer · 2024-02-20 18:16:35Z

2

As noted in the docs

Multiple result set support is available from a raw DBAPI cursor using the nextset method

so we need to use a .raw_connection(). For example, with a user-defined table type

CREATE TYPE dbo.my_table_type AS TABLE 
(
    id INT,
    txt NVARCHAR(50),
    PRIMARY KEY (id)
)

and a stored procedure that accepts a TVP of that type

CREATE PROCEDURE dbo.my_sp 
    @tvp dbo.my_table_type READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT id, txt FROM @tvp;
    SELECT N'second result set' AS info;
END

we can get the results like this:

import sqlalchemy as sa

engine = sa.create_engine(
    sa.URL.create(
        "mssql+pyodbc",
        username="scott",
        password="tiger^5HHH",
        host="192.168.0.199",
        database="test",
        query=dict(driver="ODBC Driver 17 for SQL Server")
    ),
)
tvp_data = [(1, "foo"), (2, "bar")]
dbapi_connection = engine.raw_connection()
cursor = dbapi_connection.cursor()
cursor.execute("{CALL dbo.my_sp (?)}", (tvp_data,))
print(cursor.fetchall())  # [(1, 'foo'), (2, 'bar')]
cursor.nextset()
print(cursor.fetchall())  # [('second result set',)]
cursor.close()
dbapi_connection.close()

answered Feb 20, 2024 at 18:16

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user23449623 Over a year ago

Thank you! This did the trick. Where did you found in the docs the syntax for sending the TVP?

Collectives™ on Stack Overflow

Python call procedure with TVP parameter that returns multiple tables in SQL server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related