存储过程与游标 结合在一起使用

--drop proc insertpostcode

create proc insertpostcode (
@districtname nvarchar(50),--地区名称
@provincename   nvarchar(50),--省名
@districtpostcode varchar(20)--邮编号码
)
as
declare @districtcode varchar(20)--地区编号
--给地区编号赋值
set @districtcode=(select distinct districtcode  from district where  citypostcodeid in(
select citypostcodeid from city where provincepostcode in(
select provincepostcode from province where provincename=@provincename)
)
and districtname=@districtname )
--城市编号
declare @citypostcodeid varchar(20)
--给城市编号赋值
set @citypostcodeid=(select distinct citypostcodeid  from district where  citypostcodeid in(
select citypostcodeid from city where provincepostcode in(
select provincepostcode from province where provincename=@provincename)
)
and districtname=@districtname)
--地区编号不为空。说明存在此地区
if( @districtcode!='')
BEGIN
 --记录总数
 DECLARE @count int
 SET @count=(SELECT count(*) FROM district WHERE district.districtpostcode=@districtpostcode)
 --PRINT @count
 --说明此邮编在数据中不存在
 IF(@count=0)
 begin
           --插入到district 表中
           insert into district values(@districtpostcode,@districtcode,@districtname,@citypostcodeid,0,0,null,null,null,null,null)
           select * from district where districtpostcode =@districtpostcode
           --areassign 下插入新的记录
              DECLARE  @areacount int
      SET @areacount=(SELECT  count(*) FROM areaassign WHERE districtpostcode IN(
             SELECT district.districtpostcode
                FROM district
                 WHERE district.districtname=@districtname  ))
                 --PRINT @areacount
      IF(@areacount!=0)--已经分配
      begin
         declare     @provinceid     varchar(20)   --省邮编
           DECLARE     @areaid         varchar(10)           --区域编号
           declare     @sellerid        varchar(10)             --所属销售人员
           declare     @rankid          varchar(10)            --级别
           declare     @groupid          varchar(10)          -- 组编号
           declare   kk   cursor   for    
           SELECT  TOP 1 areaassign.provincepostcode,areaassign.areaid,areaassign.sellerid,rankid,groupid
              FROM areaassign
              WHERE districtpostcode
              IN
              (
               SELECT district.districtpostcode
               FROM district
               WHERE district.districtname=@districtname
              ) 
            open   kk  
            fetch     next   from     kk   into   @provinceid,@areaid ,@sellerid,@rankid,@groupid
            while   @@fetch_status=0  
            begin  
            fetch     next   from     kk   into    @provinceid,@areaid ,@sellerid,@rankid,@groupid
            --PRINT  @provinceid
            -- PRINT @areaid
            -- PRINT  @sellerid
            -- PRINT  @rankid
            -- PRINT  @groupid
            INSERT INTO areaassign
            (
             --assignareaid,
             provincepostcode,
             citypostcodeid,
             districtpostcode,
             areaid,
             sellerid,
             technicianid,
             remark,
             isdeleted,
             createtime,
             createby,
             updatetime,
             updateby,
             rankid,
             groupid
            )
            VALUES
            (
             @provinceid,
             @citypostcodeid,
             @districtpostcode,
             @areaid,
             @sellerid,
             NULL,
             NULL,
             0,
             getdate(),
             NULL,
             getdate(),
             NULL,
             @rankid,
             @groupid
            )
            end  
            close   kk  
            deallocate     kk
      end
        else --还没有分配
         BEGIN
           PRINT @districtname +'未分配'
                
          
          end 
          --插入到areassign 表中结束
          --插入到areadetails 表中开始
         declare @areadetailscount int--总数
         set @areadetailscount =(select count(*) from areadetails where citypostcodeid=@citypostcodeid)
         print @areadetailscount

         if(@areadetailscount!=0)
          begin
          declare     @provin     varchar(10)  
            DECLARE @aareaid varchar(10)
            DECLARE @agroupid varchar(10)
           declare   areadetailscur   cursor   for    
           select top 1  provincepostcode,areaid,groupid from dbo.areadetails where citypostcodeid=@citypostcodeid
           open   areadetailscur  
           fetch     next   from     areadetailscur   into  @provin,@aareaid,@agroupid
           while   @@fetch_status=0  
           begin  
           fetch     next   from     areadetailscur   into   @provin,@aareaid,@agroupid
          INSERT INTO areadetails
           (
            --areadetailsid,
            provincepostcode,
            citypostcodeid,
            districtpostcode,
            areaid,
            remark,
            isdeleted,
            createtime,
            createby,
            updatetime,
            updateby,
            groupid
           )
           VALUES
           (
            @provin,
            @citypostcodeid,
            @districtpostcode,
            @aareaid,
            null,
            0,
            getdate(),
            null,
            getdate(),
            null,
            @agroupid
           )
           end  
           close   areadetailscur  
           deallocate     areadetailscur
          end
          else
           begin
             PRINT @districtname +'未分配'
           end            
   
 END
  ELSE
   BEGIN
   PRINT @districtpostcode+'邮编已经存在'
   END 
end
else
begin
print  @districtname+ '区域不存在'
end

--exec insertpostcode '滨湖区','江苏省','214066'

--delete from district where districtpostcode='214066'
--DELETE FROM areaassign WHERE  districtpostcode='214066'
--DELETE FROM areadetails WHERE districtpostcode='214066'
--select * from district where districtname='江苏省'

--SELECT * FROM areaassign WHERE districtpostcode='410083'
--SELECT * FROM areaassign WHERE citypostcodeid='410083'

--SELECT * FROM areadetails WHERE districtpostcode='410083'
--SELECT * FROM areadetails WHERE  citypostcodeid='214000'
--SELECT * FROM district WHERE district.districtpostcode='410083'
--SELECT * FROM district WHERE district.districtname='岳麓区'