ALTER proc [dbo].[LoadParcelFromInvestment]
as
declare @ParcelName nvarchar(50)--定义地块名
declare @ParcelOriginalID nvarchar(50)--定义地块ID
declare @ParcelGainDate nvarchar(50)--定义地块创建时间
declare @ProjectCode nvarchar(50)--定义地块对应的项目code
DECLARE My_Cursor CURSOR --定义游标
for(SELECT Id,Name,TakeTime FROM [LK_Investment].[ZL_Investment].[dbo].[Viw_TakedLandInfo]) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
fetch next from My_Cursor into @ParcelOriginalID,@ParcelName,@ParcelGainDate
--FETCH NEXT FROM My_Cursor ; --读取第一行数据
WHILE @@FETCH_STATUS = 0
BEGIN
if exists(select 1 from MDS_Parcel_Investment where ParcelName = @ParcelName and Status='0')--如果status=0表示状态未拿地进行修改
begin
--修改地块表的状态
print 1
--update [dbo].[MDS_Parcel_Investment] set Status=1 where ProjectCode=@ProjectCode
update MDS_Parcel_Investment set status=1 where ParcelName=@ParcelName
print 2
--通过每条数据循环的地块名查询项目code然后给变量赋值
set @ProjectCode= (select top 1 ProjectCode from MDS_Parcel_Investment where ParcelName = @ParcelName)
--修改地块数据
update MDS_Parcel_Investment set ParcelGainDate=@ParcelGainDate, ParcelOriginalID=@ParcelOriginalID where ParcelName=@ParcelName
print 3
--根据项目code查询如果有一块地块查到有状态0未拿地修改项目表跟信息表
if not exists(select*from [dbo].[MDS_Parcel_Investment] where ProjectCode=@ProjectCode and Status='0')
begin
--修改项目信息表的状态
update [dbo].[MDS_Project_Base] set LandStatus=1 where ProjectCode=@ProjectCode
--修改项目表的状态
update [dbo].[MDS_Project] set LandStatus=1 where ProjectCode=@ProjectCode
print 4
end
end
else--如果不存在未拿地新增
begin
insert into [MDS_Parcel_Investment] (ParcelOriginalID,ParcelName,ParcelGainDate,ParcelType,AreaGroup,AreaGroupName,AreaCompany,AreaCompanyName,BuCode,BuName,areaid,area,cityid,city,provinceid,province)
SELECT Id,[Name],[TakeTime] ,[LandType],[qyjtCode],[qyjtName],[qygsCode] ,[qygsName],[sybCode],[sybName],t.areaid,t.area,t.cityid,t.city,t.provinceid,t.province FROM [LK_Investment].[ZL_Investment].[dbo].[Viw_TakedLandInfo] t
left join MDS_Parcel_Investment p
on cast(t.Id as nvarchar(200))=p.ParcelOriginalID where p.ParcelOriginalID is null and Name=@ParcelName
end
begin
update [MDS_Parcel_Investment] set Status=1 where ParcelName=@ParcelName
end
--UPDATE dbo.MemberAccount SET UserName = UserName + 'A' WHERE CURRENT OF My_Cursor; --更新
--DELETE FROM dbo.MemberAccount WHERE CURRENT OF My_Cursor; --删除
--FETCH NEXT FROM My_Cursor; --读取下一行数据
fetch next from My_Cursor into @ParcelOriginalID,@ParcelName,@ParcelGainDate
END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标