省市区三级的存储过程，自动添加地区的存储过程问题

[szlixiaolong]

创建省的表结构：
CREATE TABLE [G_Cfg_SndMap_Province] (
[ProvinceID] [int] NOT NULL CONSTRAINT [DF_G_Cfg_SndMap_Province_ProvinceID] DEFAULT (0),
[ProvinceName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_Province_ProvinceName] DEFAULT (''),
[ProvinceName2] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_Province_ProvinceName2] DEFAULT (''),
[Remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_Province_Remark] DEFAULT (''),
[DefaultLogistic] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_Province_DefaultLogistic] DEFAULT (''),
CONSTRAINT [PK_G_Cfg_SndMap_Province] PRIMARY KEY CLUSTERED
(
[ProvinceID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO

创建市的表结构：
CREATE TABLE [G_Cfg_SndMap_City] (
[CityID] [int] NOT NULL ,
[ProvinceID] [int] NULL CONSTRAINT [DF_G_Cfg_SndMap_City_ProvinceID] DEFAULT (0),
[CityName] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_City_CityName] DEFAULT (''),
[CityName2] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_City_CityName2] DEFAULT (''),
[Remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_City_Remark] DEFAULT (''),
[DefaultLogistic] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_City_DefaultLogistic] DEFAULT (''),
CONSTRAINT [PK_G_Cfg_SndMap_City] PRIMARY KEY CLUSTERED
(
[CityID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO


创建区的表结构内容 ：
CREATE TABLE [G_Cfg_SndMap_Area] (
[AreaID] [int] NOT NULL ,
[CityID] [int] NULL ,
[AreaName] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_Area_AreaName] DEFAULT (''),
[AreaName2] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_Area_AreaNam2] DEFAULT (''),
[Remark] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_Area_Remark] DEFAULT (''),
[DefaultLogistic] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_Area_DefaultLogistic] DEFAULT (''),
[LogisticArea] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_G_Cfg_SndMap_Area_LogisticArea] DEFAULT (''),
CONSTRAINT [PK_G_Cfg_SndMap_Area] PRIMARY KEY CLUSTERED
(
[AreaID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO



要在添加地区的存储过程，

declare @ProvinceID int ,@CityID int,@AreaID int
declare @MaxAredID int
select @ProvinceID = 0 , @CityID = 0,@AreaID = 0
Select top 1 @ProvinceID = ProvinceID from G_Cfg_SndMap_Province where ProvinceName like '江苏%'
if @@ROWCOUNT > 0
begin
Select top 1 @CityID = CityID from G_Cfg_SndMap_City where ProvinceID = @ProvinceID and CityName like '扬州%'
if @@ROWCOUNT > 0
begin
Select top 1 @AreaID = AreaID from G_Cfg_SndMap_Area where CityID = @CityID and AreaName = '江都市'
if @@ROWCOUNT > 0
begin
if not EXISTS(Select * from G_Cfg_SndMap_Area where CityID = @CityID and AreaName = '江都区')
update G_Cfg_SndMap_Area set AreaName = '江都区' where AreaID = @AreaID and CityID = @CityID
end
else
begin
if not EXISTS(Select * from G_Cfg_SndMap_Area where CityID = @CityID and AreaName = '江都区')
begin
set @MaxAredID = 0
Select @MaxAredID = MAX(AreaID) from G_Cfg_SndMap_Area where CityID = @CityID
insert into G_Cfg_SndMap_Area(AreaID,CityID,AreaName) values(@MaxAredID + 1,@CityID,'江都区')
end
end
end
Select top 1 @CityID = CityID from G_Cfg_SndMap_City where ProvinceID = @ProvinceID and CityName like '苏州%'
if @@ROWCOUNT > 0
begin
Select top 1 @AreaID = AreaID from G_Cfg_SndMap_Area where CityID = @CityID and AreaName = '吴江市'
if @@ROWCOUNT > 0
begin
if not EXISTS(Select * from G_Cfg_SndMap_Area where CityID = @CityID and AreaName = '吴江区')
update G_Cfg_SndMap_Area set AreaName = '吴江区' where AreaID = @AreaID and CityID = @CityID
end
else
begin
if not EXISTS(Select * from G_Cfg_SndMap_Area where CityID = @CityID and AreaName = '吴江区')
begin
set @MaxAredID = 0
Select @MaxAredID = MAX(AreaID) from G_Cfg_SndMap_Area where CityID = @CityID
insert into G_Cfg_SndMap_Area(AreaID,CityID,AreaName) values(@MaxAredID + 1,@CityID,'吴江区')
end
end

Select top 1 @AreaID = areaid from G_Cfg_SndMap_Area where CityID = @CityID and (AreaName like '平江%' or AreaName like '沧浪%' or AreaName like '金阊%')
if @@ROWCOUNT > 0
begin
if not EXISTS(Select * from G_Cfg_SndMap_Area where CityID = @CityID and AreaName = '姑苏区')
begin
update G_Cfg_SndMap_Area set AreaName = '姑苏区' where CityID = @CityID and AreaID = @AreaID
delete G_Cfg_SndMap_Area where CityID = @CityID and (AreaName like '平江%' or AreaName like '沧浪%' or AreaName like '金阊%')
end
end
else
begin
if not EXISTS(Select * from G_Cfg_SndMap_Area where CityID = @CityID and AreaName = '姑苏区')
begin
set @MaxAredID = 0
Select @MaxAredID = MAX(AreaID) from G_Cfg_SndMap_Area where CityID = @CityID
insert into G_Cfg_SndMap_Area(AreaID,CityID,AreaName) values(@MaxAredID + 1,@CityID,'姑苏区')
end
end
end

end

Select top 1 @ProvinceID = ProvinceID from G_Cfg_SndMap_Province where ProvinceName like '贵州%'
if @@ROWCOUNT > 0
begin
Select top 1 @CityID = CityID from G_Cfg_SndMap_City where ProvinceID = @ProvinceID and CityName = '毕节地区'
if @@ROWCOUNT <= 0
begin
Select top 1 @CityID = CityID from G_Cfg_SndMap_City where ProvinceID = @ProvinceID and CityName = '毕节市'
if @@ROWCOUNT > 0
begin
if not EXISTS(Select * from G_Cfg_SndMap_Area where CityID = @CityID and AreaName = '七星关区')
begin
if EXISTS(Select * from G_Cfg_SndMap_Area where CityID = @CityID and AreaName = '毕节市')
update G_Cfg_SndMap_Area set AreaName = '七星关区' where CityID = @CityID and AreaName = '毕节市'
else
begin
set @MaxAredID = 0
Select @MaxAredID = MAX(AreaID) from G_Cfg_SndMap_Area where CityID = @CityID
insert into G_Cfg_SndMap_Area(AreaID,CityID,AreaName) values(@MaxAredID + 1,@CityID,'七星关区')
end
end
end
end
else
begin
if not EXISTS(Select * from G_Cfg_SndMap_Area where CityID = @CityID and AreaName = '七星关区')
begin
if EXISTS(Select * from G_Cfg_SndMap_Area where CityID = @CityID and AreaName = '毕节市')
update G_Cfg_SndMap_Area set AreaName = '七星关区' where CityID = @CityID and AreaName = '毕节市'
else
begin
set @MaxAredID = 0
Select @MaxAredID = MAX(AreaID) from G_Cfg_SndMap_Area where CityID = @CityID
insert into G_Cfg_SndMap_Area(AreaID,CityID,AreaName) values(@MaxAredID + 1,@CityID,'七星关区')
end
end
end
if not Exists(Select * from G_Cfg_SndMap_City where ProvinceID = @ProvinceID and CityName = '毕节市')
update G_Cfg_SndMap_City set CityName = '毕节市' where ProvinceID = @ProvinceID and CityName = '毕节地区'
end

go

[szlixiaolong]

请大家帮忙修改一个存储过程，最终要实现自动添加相应区域的名称，编号。 320000 江苏省 --省表的内容 321000 320000 扬州市 --市表的内容 321088 321000 江都市 --区表的内容

[szlixiaolong]

引用 1 楼 szlixiaolong 的回复:

请大家帮忙修改一个存储过程，最终要实现自动添加相应区域的名称，编号。 320000 江苏省 --省表的内容 321000 320000 扬州市 --市表的内容 321088 321000 江都市 --区表的内容

说错了，不是存储过程，是声明变量以及进行关于区的SQL语句插入