我的存储过程回滚得足够远吗？

发布于 2024-11-28 03:48:57 字数 511 浏览 0 评论 0原文

我正在通过带有 odbc 连接的 php 使用 sql server 2008 数据库。

我正在读取数据文件并将它们记录到数据库中，但由于文件大小/布局不同，我的 sql 是自动生成的。

sql 按以下顺序调用：

set autocommit to off
execute some sql 
execute more sql 
execute a SP.
commit

在我的存储过程中，我想使用 try catch 进行错误处理，如下所示：

BEGIN try
    --sql
END try
BEGIN catch
    rollback
END catch

我想知道这是否会仅回滚 sp 并让我的其他 sql 提交，还是会返回到该点自动提交在哪里设置为关闭？

另一种可能的解决方案是从存储过程返回 false/true 并使用它从 php.ini 调用回滚。这可能吗？如果是这样，如何使用存储过程返回一个值？

原文

I am working with a sql server 2008 database through php with an odbc connection.

I am reading data files and logging them into the database but due to varied file sizes/layouts, my sql is somewhat automatically generated.

The sql is called in this order:

set autocommit to off
execute some sql 
execute more sql 
execute a SP.
commit

in my stored procedure I want to do error handling with a try catch like so:

BEGIN try
    --sql
END try
BEGIN catch
    rollback
END catch

I am wondering will this roll back only the sp and leave my other sql to commit or will it go back to the point where autocommit was set to off?

another possible solution would be to return a false/true from the stored procedure and use that to call a rollback from php. is this possible? if so, how does one return a value with a stored procedure?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

—━☆沉默づ 2024-12-05 03:48:57

AFAIK，如果您将希望回滚的所有内容包装到事务中，那么您就知道可以回滚那么远。但是，请注意您的被调用过程提交自己的事务 - 请参阅此处了解更多。

例如，使用您提供的内容：

DECLARE @success bit
BEGIN TRAN T1

    BEGIN try
        EXEC [dbo].[usp_MyProc]
        SET @success = 1
        PRINT 'SUCCESS'
    END try
    BEGIN catch
        SET @success = 0
        PRINT 'FAIL'
    END catch

    IF @success = 1
        BEGIN
            COMMIT TRAN T1
        END
    ELSE
        BEGIN
            ROLLBACK TRAN T1
        END
PRINT @@TRANCOUNT

在这两种情况下，最终的TRANCOUNT 都应该为 0。

AFAIK, provided you wrap everything that you wish to rollback into a transaction then you know that you can rollback that far. Beware of your called procs COMMITting their own transactions, however - see here for more.

For example, using what you provided:

DECLARE @success bit
BEGIN TRAN T1

    BEGIN try
        EXEC [dbo].[usp_MyProc]
        SET @success = 1
        PRINT 'SUCCESS'
    END try
    BEGIN catch
        SET @success = 0
        PRINT 'FAIL'
    END catch

    IF @success = 1
        BEGIN
            COMMIT TRAN T1
        END
    ELSE
        BEGIN
            ROLLBACK TRAN T1
        END
PRINT @@TRANCOUNT

should end up with a TRANCOUNT of 0 in both cases.

回复收藏 0 原文