pyodbc，使用表变量调用存储过程 [英] pyodbc, call stored procedure with table variable

问题描述

我必须使用表变量参数调用 MS SQLServer 存储过程.

I have to call a MS SQLServer stored procedure with a table variable parameter.

/* Declare a variable that references the type. */
DECLARE @TableVariable AS [AList];

/* Add data to the table variable. */
INSERT INTO @TableVariable (val) VALUES ('value-1');
INSERT INTO @TableVariable (val) VALUES ('value-2');


EXEC  [dbo].[sp_MyProc]
            @param = @TableVariable

在 SQL Sv Management Studio 中运行良好.我使用 PyOdbc 在 python 中尝试了以下操作:

Works well in the SQL Sv Management studio. I tried the following in python using PyOdbc:

cursor.execute("declare @TableVariable AS [AList]")
for a in mylist:
    cursor.execute("INSERT INTO @TableVariable (val) VALUES (?)", a)
cursor.execute("{call dbo.sp_MyProc(@TableVariable)}")

出现以下错误:错误 42000:必须声明表变量.该变量在不同的执行步骤中不存在.我也试过:

With the following error: error 42000 : the table variable must be declared. THe variable does not survive the different execute steps. I also tried:

sql = "DECLARE @TableVariable AS [AList]; "
for a in mylist:
    sql = sql + "INSERT INTO @TableVariable (val) VALUES ('{}'); ".format(a)
sql = sql + "EXEC  [dbo].[sp_MyProc]   @param = @TableVariable"
cursor.execute(sql)

出现以下错误:没有结果.以前的 SQL 不是查询.没有机会了

With the following error: No results. Previous SQL was not a query. No more chance with

sql = sql + "{call dbo.sp_MyProc(@TableVariable)}"

有人知道如何使用 Pyodbc 处理这个问题吗?

does somebody knows how to handle this using Pyodbc?

推荐答案

现在问题的根源在于 SQL Server variable 具有定义它的 batch 的范围.对 cursor.execute 的每次调用都是一个单独的批处理，即使它们在同一个交易.

Now the root of your problem is that a SQL Server variable has the scope of the batch it was defined in. Each call to cursor.execute is a separate batch, even if they are in the same transaction.

有几种方法可以解决这个问题.最直接的方法是重写 Python 代码，以便将所有内容作为单个批次发送.(我在我的测试服务器上对此进行了测试，只要您添加 set nocount 或使用 nextset.)

There are a couple of ways you can work around this. The most direct is to rewrite your Python code so that it sends everything as a single batch. (I tested this on my test server and it should work as long as you either add set nocount on or else step over the intermediate results with nextset.)

更间接的方法是重写过程以查找临时表而不是表变量，然后只创建和填充临时表而不是表变量.未创建的 临时表存储过程内部具有创建它的会话的范围.

A more indirect way is to rewrite the procedure to look for a temp table instead of a table variable and then just create and populate the temp table instead of a table variable. A temp table that is not created inside a stored procedure has a scope of the session it was created in.

这篇关于pyodbc，使用表变量调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！