游标事务模板

 

----游标模板,存储过程中使用较多,游标也是一种变量类型
DECLARE @id int,@name varchar(50);
DECLARE myCursor CURSOR
 FOR SELECT ItemID,Operator FROM Table1
OPEN myCursor 
FETCH myCursor INTO @id,@name
  WHILE @@fetch_status=0 --/0或-1
    BEGIN 
      PRINT CAST(@id AS VARCHAR(50))+ @name
      FETCH myCursor INTO @id,@name
    END
CLOSE myCursor
DEALLOCATE myCursor

---- 事务模板，SQL SERVER 2005 新持的try catch
 --1.
    BEGIN TRY
       BEGIN TRAN
          --SQL OPERATION
       COMMIT TRAN
    END TRY
    BEGIN CATCH
       ROLLBACK TRAN
       PRINT @@ERROR
    END CATCH
 --2. 
      BEGIN TRANSACTION
       BEGIN
         --SQL OPERATION
       END
    IF @@ERROR=0    --  @@ERROR<>0 错误发生
      COMMIT TRANSACTION
    ELSE
      ROLLBACK TRANSACTION