Pyodbc execute sqlserver stored procedure -- how to pass in DEFAULT parameter for User Defined Table

Question

I have the following User Defined Table, as you can see I have 2 columns have DEFAULT values:

CREATE TYPE dbo.mytable AS TABLE(
[MYID] BIGINT,
[MYVALUE] INT,

[CURRENT_INDEX] BIT default 1,
[CURRENT_TS] DATETIME2(7) default CURRENT_TIMESTAMP

)

Here is my dummy Stored Procedure:

CREATE PROCEDURE MY_PROCEDURE  @temp_table dbo.mytable READONLY
    AS
    BEGIN
        INSERT INTO dbo.main_table
        (MYID,MYVALUE,CURRENT_INDEX,CURRENT_TS)
        SELECT * FROM @temp_table as tt
    END
    GO

And I'm trying to execute this stored procedure by passing in the user defined table from Python using pyodbc. As you can see, I'm trying to pass in only MYID and MYVALUE to the stored procedure and I expect the user defined table to auto-fill the default values without passing in.

def _insert_stored_procedure(cursor, table_val_parameter: list):
    """
    Call the stored procedure for table insert/update
    """
    cursor.execute("{CALL MY_PROCEDURE (?)}",
                   (table_val_parameter,))
    cursor.commit()



conn = DBCONN_Singleton(connection_type)
cursor = conn.cursor
_tvp = [[1,100],[2,150],[3,200]]
_insert_update_stored_procedure(cursor, _tvp)

Clearly the above failed with the following error:

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Trying to pass a table-valued parameter with 2 column(s) where the corresponding user-defined table type requires 4 column(s). (500) (SQLParamData)')

So my questions are:

Given the above scenario, how can I pass in only MYID and MYVALUE to let the store procedure or user defined table to auto-fill the missing values with the DEFAULT
If not, how can I pass the default values from python, for example, I want to do something like:

_tvp = [[1,100,DEFAULT,DEFAULT],[2,150,DEFAULT,DEFAULT],[3,200,DEFAULT,DEFAULT]]

user_2144390 · Answer

AFAIK there is currently no mechanism – e.g., something like pyodbc.DEFAULT – to represent a literal DEFAULT as would be used stored procedure calls. This workaround is a bit clunky but it seems to do the job:

cnxn.autocommit = True
crsr = cnxn.cursor()
crsr.fast_executemany = True

# (remove previous test data)
crsr.execute("TRUNCATE TABLE dbo.main_table")

data = ([1 ,100], [2, 150],[3, 200])
crsr.execute("CREATE TABLE #temp (MYID bigint, MYVALUE int)")
crsr.executemany("INSERT INTO #temp (MYID, MYVALUE) VALUES (?, ?)", data)
sql = """\
SET NOCOUNT ON;
DECLARE @tvp dbo.mytable;
INSERT INTO @tvp (MYID, MYVALUE) SELECT MYID, MYVALUE FROM #temp
EXEC dbo.MY_PROCEDURE @tvp
"""
crsr.execute(sql)

pprint(crsr.execute("SELECT * FROM dbo.main_table").fetchall())
"""console output:
[(1, 100, True, datetime.datetime(2020, 7, 22, 12, 17, 14, 623333)),
 (2, 150, True, datetime.datetime(2020, 7, 22, 12, 17, 14, 623333)),
 (3, 200, True, datetime.datetime(2020, 7, 22, 12, 17, 14, 623333))]
"""

user_4757432 · Answer

Besides @Gord Thompson's answer, I also found another approach and it's actually pretty simple:

First, drop the defaults from the user-defined table type:

CREATE TYPE dbo.mytable AS TABLE(
[MYID] BIGINT,
[MYVALUE] INT)

Stored Procedure:

CREATE PROCEDURE MY_PROCEDURE  @temp_table dbo.mytable READONLY
    AS
        BEGIN
            INSERT INTO dbo.main_table
            (MYID,MYVALUE,CURRENT_INDEX,CURRENT_TS)
            SELECT *,1,CURRENT_TIMESTAMP FROM @temp_table as tt
END
GO

Pyodbc execute sqlserver stored procedure -- how to pass in DEFAULT parameter for User Defined Table

2 Answers