Support for sql stored procedure table typed parameters

[WisniowskiPiotr]

Hello
I have a small request as I did not find anything similar in documentation (or did I overlook sth?).

Env

• Python: 3.6 x64
• pyodbc: 4.0.23
• OS: Windows 10
• DB: Sql Server 2016 - Compatibility lvl 130
• driver: ODBC Driver 13 for SQL Server

Observed behavior:

One to insert many rows to DB's table must use executemany with fast_executemany option and this is quite error prone as does some data converting and includes many round trips to SQL.

Expected behavior:

I would like to use Sql table types as stored procedure parameters in which case whole data set could be potentially passed from Pyton to Sql with one round trip.

Example:
Pyton:

dbAdapter = pyodbc.connect(connectionString).cursor()
dbAdapter.execute("EXEC [dbo].[StoredProcedureName] ?", listOfData)

Sql:

CREATE TYPE [dbo].[StoredProcedureNameParameterType] AS TABLE (
	[C_SomeColumn] [int] NOT NULL,
	[C_SomeColumn2] [nvarchar](max) NOT NULL
);
GO

CREATE PROCEDURE [dbo].[StoredProcedureName]
	@TBL_OfInsertedData [dbo].[StoredProcedureNameParameterType]  READONLY
AS
BEGIN
	BEGIN TRANSACTION
		INSERT INTO [dbo].[TBL_ExistingSqlTaqble] ([C_SomeColumn], [C_SomeColumn2])
		SELECT TBL_OfInsertedData.[C_SomeColumn], TBL_OfInsertedData.[C_SomeColumn2]
		FROM @TBL_OfInsertedData AS TBL_OfInsertedData
	COMMIT TRANSACTION
END
GO

Note:

This can already be done in C# and other languages, so I think there should be an option to code this (Or is it already possible)? This potentially can close all issues regarding executemany with Sql Server. Can similar techniques be used on other DBs engines?

[v-chojas]

#290

[mkleehammer]

Since this is a duplicate, I'll close this one and #290 should be followed for updates. Thanks.