Calling a T-SQL stored procedure with THROW logic from PYODBC rolls back-CodePudding

I'm running into some stranger behavior from PYODBC. I have a really simple stored procedure in SQL that does the following:

CREATE PROCEDURE [schema].[procname]
(
    @severity       VARCHAR(1) = 'I',
    @LogMessage     VARCHAR(MAX)
)
AS
BEGIN

    INSERT INTO [schema].[table]
            (col1
            ,col2)
        VALUES
            (@severity
            ,@LogMessage
            )

    IF @severity IN ('E','F')
    BEGIN;
        THROW 320000, @msg, 1
    END

END
GO

As you can see, whatever is passed to the parameters is simply inserted into a table. Whenever the @severity is 'E' or 'F', it should throw an error (after inserting).

Now this works fine just calling the procedure in SQL Server Management Studio:

EXEC [schema].[procname] @severity='E', @LogMessage = 'XYZ'

It will return an error, but the record is inserted.

But whenever I call the procedure from PYODBC:

pyodbc_cursor.execute(f"EXEC [schema].[procname] @severity='E', @LogMessage = 'XYZ'")
pyodbc_cursor.commit()

I only get the error, and the record is NOT inserted.

I can't explain this behavior. Does someone have any idea what could be wrong?

Thanks in advance!

CodePudding user response：

You should not use throw to pass information back from your stored procedure, if that is what you want then let the procedure return information like this for example

IF @severity IN ('E','F')
BEGIN
    --THROW 320000, @msg, 1
    select @msg as result
END
else
begin
    select 'no errors' as result
end

and then just read the result of the procedure after calling it.
And then you can still throw an exception in your python code should you whish so

CodePudding user response：

Python uses autocommit=false, so the driver will execute SET IMPLICIT_TRANSACTIONS ON;. This means that the whole batch is inside a transaction by default. When you throw an error it gets rolled back, removing the insert.

Either set autocommit=true in the connection settings, or use another method to return error messages.