使用游标的存储过程

USE [ChinaMDM_0603]
GO
/****** Object:  StoredProcedure [dbo].[Import_11_WorkFlow_4n_Items_CustomerCompCodes]    Script Date: 2016/6/7 13:49:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Import_11_WorkFlow_4n_Items_CustomerCompCodes]
--参数（目前不用参数）
--@parameter nvarchar(100),
--@headerid int 
as
begin 
set xact_abort on 
begin tran 
--变量
--CustomerCompCodes 表
declare @CustomerHeaderid int;
declare @CompanyCodeid nvarchar(50);
declare @ProductClasses nvarchar (100);
declare @IsISO bit;
declare @IsGMP bit;
declare @IsGsp bit;
declare @IsGMPGSPOther bit;
declare @GMPGSPOtherValue nvarchar(500);
declare @count int;
declare @CustomerCompCodeId int;
--声明游标
declare _cur cursor for
--查询必要数据
select 
h.id as Headerid,
cp.id as CompanyCodeid,
temp.[ProductClasses],
temp.IsISO,
temp.[IsGMP],
temp.[IsGSP] ,
temp.[IsGMPGSPOther],
temp.[GMPGSPOtherValue]
from temp_CustomerCompCodes_M temp
join  CustomerHeaders h on temp.CustomerHeaderCode=h.code
left join CompanyCodes cp on temp.CompanyCodes=cp.code
--打开游标
open _cur
--将游标所在行的column值赋给对应参数
fetch next from _cur into @CustomerHeaderid,@CompanyCodeid,@ProductClasses,@IsISO,@IsGMP,@IsGSP,@IsGMPGSPOther,@GMPGSPOtherValue
--判断游标状态@@fetch_status值的改变是通过fetch next from实现的   “FETCH NEXT FROM Cursor” 
--0 FETCH 语句成功 -1 FETCH 语句失败或此行不在结果集中 -2 被提取的行不存在
while(@@FETCH_STATUS=0)
begin 
select @count = COUNT(1) from CustomerCompCodes where CustomerHeaderId=@CustomerHeaderId and CompanyCodeId=@CompanyCodeid
    if ( @count > 0)
        begin
         select @CustomerCompCodeId=id from CustomerCompCodes where CustomerHeaderId=@CustomerHeaderid and CompanyCodeId=@CompanyCodeid
        update CustomerCompCodes 
        set CustomerHeaderId=@CustomerHeaderid,ProductClasses=@ProductClasses,IsISO=@IsISO,IsGMP=@IsGMP,IsGSP=@IsGsp,IsGMPGSPOther=@IsGMPGSPOther,GMPGSPOtherValue=@GMPGSPOtherValue 
        where id=@CustomerCompCodeId
        end
    else 
        begin
        --将参数值插入表VendorCompCodes
insert into CustomerCompCodes (CustomerHeaderId,CompanyCodeid,ProductClasses,IsISO,IsGMP,IsGSP,IsGMPGSPOther,GMPGSPOtherValue,IsActive) values (@CustomerHeaderid,@CompanyCodeid,@ProductClasses,@IsISO,@IsGMP,@IsGSP,@IsGMPGSPOther,@GMPGSPOtherValue,1)
        end
--循环下一行游标并将游标所在行的column值赋给对应参数
fetch next from _cur into @CustomerHeaderid,@CompanyCodeid,@ProductClasses,@IsISO,@IsGMP,@IsGSP,@IsGMPGSPOther,@GMPGSPOtherValue
end
--关闭游标
close _cur
--释放游标所占资源
deallocate _cur

--判断循环过程中是否有错，有则撤回
if @@error<>0
    begin
        rollback tran
    end
--无措则提交    
commit tran
set xact_abort off 
end