Pyodbc执行sqlserver存储过程——如何为用户定义表传递DEFAULT参数

Question

我有下面的用户定义表，正如你所看到的，我有两列有DEFAULT值：

CREATE TYPE dbo.mytable AS TABLE(
[MYID] BIGINT,
[MYVALUE] INT,
[CURRENT_INDEX] BIT default 1,
[CURRENT_TS] DATETIME2(7) default CURRENT_TIMESTAMP

)

这是我的伪存储过程：

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

我正试图通过使用pyodbc从Python传递用户定义的表来执行这个存储过程。正如您所看到的，我试图只将MYID和MYVALUE传递给存储过程，并且我希望用户定义的表在不传递的情况下自动填充默认值

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)

显然，上述失败，出现以下错误：

[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)')

所以我的问题是：

1. 给定上述场景，如何仅传入MYID和MYVALUE，让存储过程或用户定义的表使用DEFAULT 自动填充缺失的值

2. 如果没有，我如何从python传递默认值，例如，我想做一些类似的事情：

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

Answer 1

AFAIK目前没有任何机制(例如pyodbc.DEFAULT(来表示存储过程调用所使用的文字DEFAULT。这个变通方法有点笨拙，但它似乎能完成任务：

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))]
"""

Answer 2

除了@Gord Thompson的答案，我还找到了另一种方法，它实际上很简单：

首先，从用户定义的表类型中删除默认值：

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

存储过程：

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