如何在sql server中提交和回滚事务？

I have a huge script for creating tables and porting data from one server. So this sceipt basically has -

我有一个巨大的脚本，用于创建表和从一台服务器移植数据。所以这个剧本基本上有 -

Create statements for tables.
为表创建语句。
Insert for porting the data to these newly created tables.
插入以将数据移植到这些新创建的表。
Create statements for stored procedures.
为存储过程创建语句。

So I have this code but it does not work basically @@ERROR is always zero I think..

所以我有这个代码，但它基本上不起作用@@ ERROR总是零我认为..

BEGIN TRANSACTION
--CREATES
--INSERTS
--STORED PROCEDURES CREATES
    -- ON ERROR ROLLBACK ELSE COMMIT THE TRANSACTION
    IF @@ERROR != 0
        BEGIN

            PRINT @@ERROR
                      PRINT 'ERROR IN SCRIPT'
            ROLLBACK TRANSACTION
            RETURN
        END
    ELSE
    BEGIN
        COMMIT TRANSACTION
        PRINT 'COMMITTED SUCCESSFULLY'
    END
    GO

Can anyone help me write a transaction which will basically rollback on error and commit if everything is fine..Can I use RaiseError somehow here..

任何人都可以帮我写一个事务，基本上会回滚错误并提交，如果一切都很好..我可以在这里使用RaiseError ..

3 个解决方案

#1

Don't use @@ERROR, use BEGIN TRY/BEGIN CATCH instead. See this article: Exception handling and nested transactions for a sample procedure:

不要使用@@ ERROR，而是使用BEGIN TRY / BEGIN CATCH。请参阅此文章：示例过程的异常处理和嵌套事务：

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
        return;
    end catch   
end

#2

As per http://msdn.microsoft.com/en-us/library/ms188790.aspx

根据http://msdn.microsoft.com/en-us/library/ms188790.aspx

@@ERROR: Returns the error number for the last Transact-SQL statement executed.

@@ ERROR：返回最后执行的Transact-SQL语句的错误号。

You will have to check after each statement in order to perform the rollback and return.

您必须在每个语句之后检查以执行回滚和返回。

Commit can be at the end.

提交可以在最后。

HTH

#3

Avoid direct references to '@@ERROR'. It's a flighty little thing that can be lost.

避免直接引用'@@ ERROR'。这是一个可以丢失的轻浮小事。

Declare @ErrorCode int;
... perform stuff ...
Set @ErrorCode = @@ERROR;
... other stuff ...
if @ErrorCode ......

#1