Stored Procedure executemany does not return selected value

[CaptainCsaba]

• Python: 3.10.2 64bit
• pyodbc: 4.0.32
• OS: Windows 10 64bit
• DB: SQL Server (MSSQL)
• driver: 'ODBC Driver 17 for SQL Server'

I have the following stored procedure. I create some variables in the beginning that I select no matter what happens in the end as they indicate information about the success of the procedure. So I want to insert, then have the select return a row in the end. I also call this single stored procedure with executemany on multiple objects. Running the stored procedure in SQL Server Management Studio is working fine and gives back the desired select in the end no matter what. Calling the same from pyodbc only returns the select if I run execute or executemany on exactly 1 object.

Ideally I would like to receive a response for all objects I give to executemany with fast_executemany set to True. If I set fast_executemany to True I get nothing back, just 'Previous SQL was not a query'.

I have SET NOCOUNT ON in the procedure and I have also set autocommiton when creating the connection. No matter what I do,.fetchall() gives back 'pyodbc.ProgrammingError: No results. Previous SQL was not a query.' The row gets inserted correctly and everything works fine, but I am not getting back the return value in Python. Removing fastexecute_many = True will make the function give back exactly only 1 row of result, but the rest are left out. If I call the executemany function on many rows I would expect to get back a result for each insert. This leaves me with using just execute on rows one-by-one which is not ideal as I am working with many thousands of rows.

How can I use executemany with fast_executemany set to True on multiple objects with all of them getting a return value in the end in an array?

CREATE PROCEDURE [Letters].[spInsertSecretary]
(
    @role NVARCHAR(50),
    @name NVARCHAR(255),
    @source NVARCHAR(20),
    @company_id NVARCHAR(10),
    @country NVARCHAR(55) = NULL,
    @region NVARCHAR(100) = NULL,
    @postal_code NVARCHAR(20) = NULL,
    @locality NVARCHAR(100) = NULL,
    @premises NVARCHAR(100) = NULL,
    @address_line_1 NVARCHAR(255) = NULL,
    @address_line_2 NVARCHAR(255) = NULL,
    @country_of_residence NVARCHAR(55) = NULL,
    @appointed_on DATETIME2 = NULL,
    @occupation NVARCHAR(55) = NULL,
    @nationality NVARCHAR(55) = NULL,
    @date_of_birth DATETIME2 = NULL,
    @resigned_on DATETIME2 = NULL,
    @name_normal NVARCHAR(255) = NULL
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @HasError BIT = 0
    DECLARE @InsertedId INT = NULL
    DECLARE @ErrorLine INT = NULL
    DECLARE @ErrorMessage NVARCHAR(1000) = NULL

    BEGIN TRY
        INSERT INTO [Letters].[Secretary]
        (
            role,
            name,
            source,
            company_id,
            country,
            region,
            postal_code,
            locality,
            premises,
            address_line_1,
            address_line_2,
            country_of_residence,
            appointed_on,
            occupation,
            nationality,
            date_of_birth,
            resigned_on,
            name_normal,
            created_at,
            last_updated_at
        )
        VALUES
        (
            @role,
            @name,
            @source,
            @company_id,
            @country,
            @region,
            @postal_code,
            @locality,
            @premises,
            @address_line_1,
            @address_line_2,
            @country_of_residence,
            @appointed_on,
            @occupation,
            @nationality,
            @date_of_birth,
            @resigned_on,
            @name_normal,
            GETDATE(),
            GETDATE()
        )

        SET @InsertedId = SCOPE_IDENTITY()

    END TRY
    BEGIN CATCH

        SET @HasError = 1
        SET @ErrorLine = ERROR_LINE()
        SET @ErrorMessage = ERROR_MESSAGE()

    END CATCH

    SELECT 
        @InsertedId AS 'InsertedId',
        @HasError AS 'HasError',
        @ErrorMessage AS 'ErrorMessage',
        @ErrorLine AS 'ErrorLine'

END
GO

connection_string = f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={Config.DATABASE_SERVER_NAME};DATABASE={Config.DATABASE_NAME};UID={Config.DATABASE_USERNAME};PWD={Config.DATABASE_PASSWORD};trusted_connection=yes;autocommit=yes;"
query = 'EXEC Letters.spInsertSecretary @role=?, @name=?, @company_id=?, @country_of_residence=?, @appointed_on=?, @occupation=?, @nationality=?, @date_of_birth=?, @resigned_on=?, @name_normal=?, @source=?, @country=?, @region=?, @postal_code=?, @locality=?, @premises=?, @address_line_1=?, @address_line_2=?'
param_values = (('secretary', 'MARTIN, Rosemary Elisabeth Scudamore', '01833679', None, datetime.datetime(2010, 3, 30, 0, 0), None, None, None, None, None, 'COMPANIES HOUSE', None, 'Berkshire', 'RG14 2FN', 'Newbury', None, 'Vodafone House', 'The Connection'),('secretary', 'MARTIN, Rosemary Elisabeth Scudamore', '01833679', None, datetime.datetime(2010, 3, 30, 0, 0), None, None, None, None, None, 'COMPANIES HOUSE', None, 'Berkshire', 'RG14 2FN', 'Newbury', None, 'Vodafone House', 'The Connection'))
with pyodbc.connect(connection_string) as connection:
    with connection.cursor() as cursor:
        cursor.fast_executemany = True
        result = cursor.executemany(query, param_values) # result becomes None
        return_value = cursor.fetchall() # pyodbc.ProgrammingError: No results.  Previous SQL was not a query.

As I have stated, the inserts happen, but I get no result back for any of them from the select at the end.

[gordthompson]

Possibly related:

https://github.com/mkleehammer/pyodbc/wiki/Tips-and-Tricks-by-Database-Platform#passing-row-oriented-parameter-data-as-a-json-string

[CaptainCsaba]

Possibly related:

https://github.com/mkleehammer/pyodbc/wiki/Tips-and-Tricks-by-Database-Platform#passing-row-oriented-parameter-data-as-a-json-string

Is the only way to solve this is to send in all my input objects in a JSON array and then use OPENJSON? Can't the same result be achieved by normal methods?

[v-chojas]

Regular executemany() simply calls execute() in a loop internally, ignoring the result of each one. If you need the result then you may call execute() in a loop yourself and gather the results in your script.

Note that fast_executemany is optimised for bulk insertion (only), so it might not be applicable to your use-case.

[gordthompson]

.executemany() is not specifically designed to return result sets. PEP 249 says:

Use of this method for an operation which produces one or more result sets constitutes undefined behavior, and the implementation is permitted (but not required) to raise an exception when it detects that a result set has been created by an invocation of the operation.

So if you want result set(s) you need to use .execute(). You can either

• employ the OPENJSON trick,
• use a loop as suggected by @v-chojas , or
• you can modify your stored procedure to accept a table-valued parameter (TVP) and then use that in your .execute() call.

[CaptainCsaba]

Thank you for the explanation, it is much more clear now what is happening. I just have one more question as I am unfamiliar how SQL handles situations. If I need to insert a lot of lines, and I decide to use .execute(), but I want to improve performance, how bad of an idea is it to use either the threads or async methods of python to run X amount of .execute()-s together simultaneously? And if it is not a bad idea, and ordering is not important, what is the safe amount of simultaneous calls one could make? Would all of these require a separate connection and cursor?

[v-chojas]

Depending on what the bottleneck is, you will have to experiment to find what works best for your situation. The ODBC Driver for SQL Server can definitely handle being called from multiple threads simultaneously, although you may find most benefit with multiple connections.

[CaptainCsaba]

Thank you very much. I got my answers. The thread can be closed