Try to insert TVP with None in first row

[IcyTide]

Hi, I want to know is there any solution to insert data as TVP but with None in first row?

Inspired by https://github.com/mkleehammer/pyodbc/issues/596

[gordthompson]

There is no fix, but there is a workaround for SQL Server 2016+.

Given …

CREATE TYPE dbo.issue_1229_table_type AS TABLE 
(
    id int NOT NULL, 
    txt nvarchar(50) NULL, 
    PRIMARY KEY (id)
)
GO

CREATE PROCEDURE issue_1229_sp 
    @tvp dbo.issue_1229_table_type READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM @tvp;
END
GO

…

with the latest version of pyodbc — 4.0.39 at the time of writing — this works (no None in the first row)

tvp_data = [(1, "Alfa"), (2, "Bravo")]
results = crsr.execute("EXEC issue_1229_sp ?", (tvp_data,)).fetchall()
print(results)
# [(1, 'Alfa'), (2, 'Bravo')]

but this fails

tvp_data = [(1, None), (2, "Bravo")]
results = crsr.execute("EXEC issue_1229_sp ?", (tvp_data,)).fetchall()
# Invalid SQL data type (0) (SQLBindParameter)

However, with SQL Server 2016+ this works

tvp_data = [(1, None), (2, "Bravo")]
tvp_json = [dict(zip(["id", "txt"], row)) for row in tvp_data]

sql = """\
SET NOCOUNT ON;
DECLARE @tvp dbo.issue_1229_table_type;
INSERT INTO @tvp (id, txt)
SELECT id, txt FROM OPENJSON(?)
WITH (
    id int '$.id',
    txt nvarchar(50) '$.txt'
);
EXEC issue_1229_sp @tvp
"""
results = crsr.execute(sql, json.dumps(tvp_json, default=str)).fetchall()
print(results)
# [(1, None), (2, 'Bravo')]

[v-chojas]

As explained in https://github.com/mkleehammer/pyodbc/issues/596#issuecomment-520024907 , this is a known limitation of the parameter metadata discovery.