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?

Answer 1

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()