pyodbc, call stored procedure with table variable

Question

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

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

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)

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

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

does somebody knows how to handle this using Pyodbc?

Answer 1

As Timothy pointed out the catch is to use nextset().

What I have found out is that when you execute() a multiple statement query, pyodbc checks (for any syntax errors) and executes only the first statement in the batch but not the entire batch unless you explicitly specify nextset().

say your query is :

cursor.execute('select 1 '
               'select 1/0') 
print(cursor.fetchall())

your result is:

[(1, )]

but as soon as you instruct it to move further in the batch that is the syntactically erroneous part via the command:

cursor.nextset()

there you have it:

pyodbc.DataError: ('22012', '[22012] [Microsoft][ODBC SQL Server Driver][SQL Server]Divide by zero error encountered. (8134) (SQLMoreResults)')

hence solves the issue that I encountered with working with variable tables in a multiple statement query.

Answer 2

Set connection.autocommit = True and use cursor.execute() only once instead of multiple times. The SQL string that you pass to cursor.execute() must contain all 3 steps:

1. Declaring the table variable
2. Filling the table variable with data
3. Executing the stored procedure that uses that table variable as an input

You don't need semicolons between the 3 steps.

Here's a fully functional demo. I didn't bother with parameter passing since it's irrelevant, but it also works fine with this, for the record.

SQL Setup (execute ahead of time)

CREATE TYPE dbo.type_MyTableType AS TABLE(
    a INT,
    b INT,
    c INT
)
GO

CREATE PROCEDURE dbo.CopyTable
    @MyTable type_MyTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * INTO MyResultTable FROM @MyTable
END

python

import pyodbc

CONN_STRING = (
    'Driver={SQL Server Native Client 11.0};'
    'Server=...;Database=...;UID=...;PWD=...'
)

class DatabaseConnection(object):
    def __init__(self, connection_string):
        self.conn = pyodbc.connect(connection_string)
        self.conn.autocommit = True
        self.cursor = self.conn.cursor()

    def __enter__(self):
        return self.cursor

    def __exit__(self, *args):
        self.cursor.close()
        self.conn.close()

sql = (
    'DECLARE @MyTable type_MyTableType'
    '\nINSERT INTO @MyTable VALUES'
    '\n(11, 12, 13),'
    '\n(21, 22, 23)'
    '\nEXEC CopyTable @MyTable'
)

with DatabaseConnection(CONN_STRING) as cursor:
    cursor.execute(sql)

If you want to spread the SQL across multiple calls to cursor.execute(), then you need to use a temporary table instead. Note that in that case, you still need connection.autocommit = True.

Answer 3

I had this same problem, but none of the answers here fixed it. I was unable to get "SET NOCOUNT ON" to work, and I was also unable to make a single batch operation working with a table variable. What did work was to use a temporary table in two batches, but it all day to find the right syntax. The code which follows creates and populates a temporary table in the first batch, then in the second, it executes a stored proc using the database name followed by two dots before the stored proc name. This syntax is important for avoiding the error, "Could not find stored procedure 'x'. (2812) (SQLExecDirectW))".

def create_incidents(db_config, create_table, columns, tuples_list, upg_date):
    """Executes trackerdb-dev mssql stored proc.
    Args:
        config (dict): config .ini file with mssqldb conn.
        create_table (string): temporary table definition to be inserted into 'CREATE TABLE #TempTable ()'
        columns (tuple): columns of the table table into which values will be inserted.
        tuples_list (list): list of tuples where each describes a row of data to insert into the table.
        upg_date (string): date on which the items in the list will be upgraded.
    Returns:
        None
    """

    sql_create = """IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
            DROP TABLE #TempTable;
        CREATE TABLE #TempTable ({});
        INSERT INTO #TempTable ({}) VALUES {};
        """
    columns = '"{}"'.format('", "'.join(item for item in columns))
    # this "params" variable is an egregious offense against security professionals everywhere. Replace it with parameterized queries asap.
    params = ', '.join([str(tupl) for tupl in tuples_list])
    sql_create = sql_create.format(
        create_table
        , columns
        , params)
    msconn.autocommit = True
    cur = msconn.cursor()
    try:
        cur.execute(sql_create)
        cur.execute("DatabaseName..TempTable_StoredProcedure ?", upg_date)
    except pyodbc.DatabaseError as err:
        print(err)
    else:
        cur.close()
    return

create_table = """
    int_column int
    , name varchar(255)
    , datacenter varchar(25)
    """

create_incidents(
    db_config    = db_config
, create_table = create_table
, columns      = ('int_column', 'name', 'datacenter')
, cloud_list   = tuples_list
, upg_date     = '2017-09-08')

The stored proc uses IF OBJECT_ID('tempdb..#TempTable') IS NULL syntax to validate the temporary table has been created. If it has, the procedure selects data from it and continues. If the temporary table has not been created, the proc aborts. This forces the stored proc to use a copy of the #TempTable created outside the stored procedure itself but in the same session. The pyodbc session lasts until the cursor or connection is closed and the temporary table created by pyodbc has the scope of the entire session.

IF OBJECT_ID('tempdb..#TempTable') IS NULL
BEGIN
    -- #TempTable gets created here only because SQL Server Management Studio throws errors if it isn't.
    CREATE TABLE #TempTable (
        int_column int
        , name varchar(255)
        , datacenter varchar(25)
    );

    -- This error is thrown so that the stored procedure requires a temporary table created *outside* the stored proc
    THROW 50000, '#TempTable table not found in tempdb', 1;
END
ELSE
BEGIN
    -- the stored procedure has now validated that the temporary table being used is coming from outside the stored procedure
    SELECT * FROM  #TempTable;
END;

Finally, note that "tempdb" is not a placeholder, like I thought when I first saw it. "tempdb" is an actual MS SQL Server database system object.

Answer 4

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.

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.

Answer 5

I believe this error has nothing to do with sql forgetting the table variable. I've experienced this recently, and the problem was that pyodbc doesnt know how to get a resultset back from the stored procedure if the SP also returns counts for the things affected.

In my case the fix for this was to simply put "SET NOCOUNT ON" at the start of the SP.

I hope this helps.

Answer 6

I am not sure if this works and I can't test it because I don't have MS SQL Server, but have you tried executing everything in a single statement:

cursor.execute("""
DECLARE @TableVariable AS [AList];

INSERT INTO @TableVariable (val) VALUES ('value-1');
INSERT INTO @TableVariable (val) VALUES ('value-2');

EXEC [dbo].[sp_MyProc] @param = @TableVariable;
""");