本文共 1559 字，大约阅读时间需要 5 分钟。

Create PROCEDURE [dbo].[sp_Delete_TranSample]     	ASBEGIN --SET NOCOUNT ON;　－－（注意：SET NOCOUNT ON 不返回受影响的行数， 前端ExecuteNonQuery  得到的受影响行数为－１）BEGINBEGIN TRY       BEGIN TRAN	           DELETE  FROM table1 WHERE ....		DELETE  FROM table2 WHERE ....	    COMMIT TRAN END TRYBEGIN CATCH   --ROLLBACK TRAN --放在这里也可以		        IF @@TRANCOUNT > 0  --(存在未执行成功的事务，则回滚．　注１)        BEGIN            ROLLBACK TRAN	            --PRINT ERROR_MESSAGE()				        END      END CATCH END--注１：每一次Begin Transaction都会引起@@TranCount加1。而每一次Commit Transaction都会使@@TranCount减1，而RollBack Transaction会回滚所有的嵌套事务包括已经提交的事务和未提交的事务，而使@@TranCount置0。

另一例子：

Create PROCEDURE sp_copy_JobRegMasterDetail(@masterRefNo int, --Job_Reg_Master's RefNo field@JobNo AS varchar(50), --Job_Reg_Master's JobNo field@JobNoType AS varchar(20) --Job Type. default 'JS')ASBEGIN BEGIN TRY    BEGIN TRAN		 -- １.　Insert Job_Reg_Master		INSERT INTO Job_Reg_Master Values(...)			--　２. Insert Job_Reg_DetailIF @@ROWCOUNT = 0     BEGIN	   SELECT 0	   RETURN	 END	 DECLARE @RefNo_Details as INT	 DECLARE curDetail CURSOR FOR 	    SELECT RefNo FROM  Job_Reg_Details where JobNo = @JobNo and Status = 'A' Order by ItemNo       OPEN curDetail	 FETCH NEXT FROM curDetail INTO @RefNo_Details	 WHILE (@@FETCH_STATUS != -1)	   BEGIN	      		   INSERT INTO Job_Reg_Details VALUES(...)				  FETCH NEXT FROM curDetail INTO @RefNo_Details	   END	 CLOSE curDetail	 DEALLOCATE curDetail    COMMIT TRAN END TRY BEGIN CATCH   IF @@TRANCOUNT > 0     BEGIN     --PRINT ERROR_MESSAGE() 　　　ROLLBACK TRAN       SELECT 0     END END CATCHEND

转载地址：http://fimdi.baihongyu.com/