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

--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='岳麓区'