ALTER procedure [dbo].[存储过程名]
as
begin declare @townId int
begin
declare @caId nvarchar(128)
declare @error int
set @error=0
begin tran --申明事务
--申明游标为LSMP
declare TM cursor
for (select CardID from DataInput_LSMP)
--打开游标--
open tm
--开始循环游标变量--
fetch next from TM into @caId
while @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
begin
select top 1 @townId=id from sys_town where areaName like '%'+(select LEFT(LTRIM(Address),2) from DataInput_LSMP where CardID=@caId)+'%' --根据地址得到村ID
if not exists( select * from 人员基础信息 where 身份证=@caId )
Begin
--不存在
INSERT INTO [人员基础信息] (所属市,所属县,所属乡,所属村,姓名,性别,年龄,身份证,联系电话,户籍地址,是否劳动力,是否贫困人口,是否社区矫正对象,是否信访重点人员,是否精神病人员,是否涉核人员,是否刑满释放人员,是否参战人员,是否三失人员,首次识别时间)
select cityId,areaId,townId,@townId,PatientName,Gender,Age,CardID,GuardianPhone,[Address],0,0,0,0,1,0,0,0,0,'' from DataInput_LSMP where CardID=@caId
INSERT INTO [重症精神病人名单] select CardID,PsychiatricGrade,CognizanceDate,PayableAmount,GuardianName,GuardianCardID,GuardianAccount,GuardianPhone,AdmissionDate,DischargeDate,HospitalName,'',process from DataInput_LSMP where CardID=@caId
End
else
Begin
--存在
--创建保存更新信息的变量
declare @PatientName nvarchar(150),@Gender nvarchar(150),@Age nvarchar(128),@GuardianPhone nvarchar(150),@Address nvarchar(150)
--获取信息
select @PatientName=PatientName,@Gender=Gender,@Age=Age,@GuardianPhone=GuardianPhone,@Address=[Address] from DataInput_LSMP where CardID=@caId
--更新信息通过身份证号和获取到的最新信息
update [人员基础信息] set 所属村=@townId,姓名=@PatientName,性别=@Gender,年龄=@Age,身份证=@caId,联系电话=@GuardianPhone,户籍地址=@Address,是否精神病人员=1 where 身份证=@caId
INSERT INTO [重症精神病人名单] select CardID,PsychiatricGrade,CognizanceDate,PayableAmount,GuardianName,GuardianCardID,GuardianAccount,GuardianPhone,AdmissionDate,DischargeDate,HospitalName,'',process from DataInput_LSMP where CardID=@caId
End
set @error= @error + @@ERROR --记录每次运行sql后是否正确,0正确
fetch next from TM into @caId --转到下一个游标
end
if @error=0
begin
commit tran --提交事务
end
else
begin
rollback tran --回滚事务
end
close TM --关闭游标
deallocate TM --释放游标
end
end
end