[SQL] how can I iterate over SQL Server procedures that return tables and take tables as input

In SQL Server I have a procedure that takes a table as an input and returns a table as an output. For simplicity lets take such an example:

CREATE TYPE MyTableType AS TABLE
(
ID INT,
Name NVARCHAR(100),
Age INT
);

CREATE PROCEDURE InsertAndReturnData
@InputTable MyTableType READONLY
AS
BEGIN
-- Do something with the input table, e.g., selecting from it
SELECT * FROM @InputTable;
END;


This code for example works nicely in sql server:

CREATE TABLE MyTempTable (ID INT, Name NVARCHAR(100), Age INT)

INSERT INTO MyTempTable (ID, Name, Age) VALUES
(1, 'John Doe', 30),
(2, 'Jane Smith', 25),
(3, 'Mike Johnson', 40)

select * from #MyTempTable

DECLARE @InputTable MyTableType;
INSERT INTO @InputTable SELECT * FROM MyTempTable;
EXEC InsertAndReturnData @InputTable;


Now I want to retrieve this from python. Ultimatelly I am looking for something like:

cursor = conn.cursor()
sql_query_exec = """
DECLARE @InputTable MyTableType;
INSERT INTO @InputTable SELECT * FROM MyTempTable;
EXEC InsertAndReturnData @InputTable;
"""

conn = pyodbc.connect(conn_str)
cursor.execute(sql_query_exec)
rows = cursor.fetchall()
for row in rows:
print(f"ID: {row.ID}, Name: {row.Name}, Age: {row.Age}")


but python interprets every single line as a separate block and not in one transaction.

How can I iterate over procedures that return tables and take tables as input?

Continue reading...