1. Sql写法(事物+游标)--开始事务BEGIN TRAN--不显示计数信息SET NOCOUNT ONDECLARE @ProjNo varchar(50),@CusNo varchar(50)--声明游标DECLARE CRMPSContact_cursor CURSOR FOR SELECT ProjNoFROM CRMPSContactWHERE ProjNo>0--打开游标OPEN CRMPSContact_cursor--取第一行的值给专案变量: @ProjNoFETCH NEXT FROM CRMPSContact_cursor INTO @ProjNo--取得客户号select @CusNo = CusNo from CRMPSProjectM where ProjNo = @ProjNo--得到CRMPSContact中某专案号对应的客户号update CRMPSContact set CusNo = @CusNowhere ProjNo = @ProjNo--执行错误回滚if @@error!=0 begin rollback tran return end--移动游标,其它所有行更新操作(当到结尾时退出)WHILE @@FETCH_STATUS = 0BEGIN --游标移到下一行 FETCH NEXT FROM CRMPSContact_cursor INTO @ProjNo --取得客户号 select @CusNo = CusNo from CRMPSProjectM where ProjNo = @ProjNo --得到CRMPSContact中某专案号对应的客户号 update CRMPSContact set CusNo = @CusNo where ProjNo = @ProjNo --执行错误回滚 if @@error!=0 begin rollback tran return endEND--提交所有变更COMMIT TRAN--关闭游标CLOSE CRMPSContact_cursor--释放游标DEALLOCATE CRMPSContact_cursor--恢复设置SET NOCOUNT OFFGO
|