一个带事务的Sql Server存储过程例子

Create PROCEDURE [dbo].[sp_Delete_TranSample]     	
AS
BEGIN 
--SET NOCOUNT ON;　－－（注意：SET NOCOUNT ON 不返回受影响的行数， 前端ExecuteNonQuery  得到的受影响行数为－１）

BEGIN
BEGIN TRY
       BEGIN TRAN	   
        DELETE  FROM table1 WHERE ....
		DELETE  FROM table2 WHERE ....	
    COMMIT TRAN
 END TRY
BEGIN 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'
)
AS
BEGIN
 BEGIN TRY
    BEGIN TRAN	
	
 -- １.　Insert Job_Reg_Master
		INSERT INTO Job_Reg_Master Values(...)	
		
--　２. Insert Job_Reg_Detail
IF @@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 CATCH

END