无法通过调用内部包含提交的存储过程来更新数据库

Question

我有一个这样的存储过程

CREATE PROCEDURE StudentSproc
    (@StudentID VARCHAR(50),
     @Name VARCHAR(50))
AS
BEGIN
  BEGIN TRAN

  BEGIN TRY
    INSERT INTO Student(StudentID, Name)
    VALUES (@StudentID,@Name)
    COMMIT TRAN;
  END TRY
  BEGIN CATCH
     ROLLBACK TRAN
  END CATCH
END;

我正在尝试从python执行它：

db_conn_str = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';PORT=1433;DATABASE=' + database + ';UID=' + username + ';PWD=' + password
cnxn = pyodbc.connect(db_conn_str)

cursor = cnxn.cursor()
st = f"exec master.dbo.StudentSproc @StudentID = ?, @Name = ? "
s_id = "101"
name = "Charles"
params = (s_id, name)
cursor.execute(st, params)
print(f"executed sproc by {st}")

这没有错误，并且执行存储过程，但是它不会更新数据库，我很惊讶。我知道我必须在connect()调用中使用autocommit=True，但是如果存储过程中存在提交，为什么有必要这样做呢

Answer 1

没有错误，因为您正在使用try/catch。它的工作原理与其他语言中的工作原理相同-如果catch出现异常，则不会返回到客户端。它被抓住了

您可以在catch中回滚tran，然后再次throw以将错误返回给客户端

CREATE PROCEDURE StudentSproc(
@StudentID VARCHAR(50),
@Name VARCHAR(50)) 
AS
  BEGIN
    BEGIN TRAN;

    BEGIN TRY
        INSERT INTO Student(StudentID, Name)
        VALUES (@StudentID,@Name);    
    COMMIT TRAN;
    END TRY
    BEGIN CATCH
         if (@@trancount > 0) ROLLBACK TRAN;   make sure that a transaction still exists before trying to roll back
         THROW;   now that we have dealt with the transaction, return the error to the client
    END CATCH
  END;

我还注意到您在master数据库中有您的过程。您可能不希望它出现在那里，master是一个系统数据库

你还提到你知道你必须使用自动提交，但根据我们的讨论，我现在知道你实际上没有使用它，你只是想知道为什么你必须使用它。这引入了第二种可能性

在SQL Server中，唯一重要的提交是“最外层”的提交。任何“嵌套”提交实际上除了减少@@trancount的值之外，不会做任何事情

例如：

begin tran;
insert MyTable values (1);
begin tran;   this doesn't really do anything, it just increments @@trancount
insert MyTable values (2);
commit;   this does nothing other than decrement @@trancount
  if we were to execute a rollback here, all of the data would be gone
commit;   only this commit matters

另一方面，rollback的工作方式不同。单个rollback将回滚所有嵌套事务，将@@trancount值减为零

如果您启动两个事务（一个在客户端代码中，一个在存储过程中），但只发出一个提交，那么您的“真正”事务实际上仍然处于打开状态