ALTER proc [dbo].[DeletDuplicatePatient]
as
set nocount on
declare @patientid int
declare @PatientReferralInfoId int
declare @PatientReferralInfo_count intdeclare tb cursor local for select patientid from Patient
--我在这里定义了一个主循环的游标
open tb
fetch tb into @patientid
while @@fetch_status = 0
begin
select @PatientReferralInfo_count = count(*) from PatientReferralInfo where Patientid = @patientid
if(@PatientReferralInfo_count>0)
begin
declare PatientReferralInfo_tb cursor local for select PatientReferralInfoId from PatientReferralInfo where Patientid = @patientid
-- 在这里定义了一个主循环游标中的游标
open PatientReferralInfo_tb
fetch PatientReferralInfo_tb into @PatientReferralInfoId
while(@PatientReferralInfo_count>0)
begin
delete PatientPublicHealthNurse where PatientReferralInfoId=@PatientReferralInfoId
set @PatientReferralInfo_count = @PatientReferralInfo_count-1
fetch PatientReferralInfo_tb into @PatientReferralInfoId
-- 附下一个值
end
end
delete PatientReferralInfo where patientid = @patientid
delete patient where PatientId = @patientid
fetch tb into @patientid
-- 附下一个值
end这个存储过程是用在删除数据表Patient中的记录,但是它与表PatientReferralInfo通过外键有联系,所以我要先删除PatientReferralInfo中的记录,但是PatientReferralInfo又与PatientPublicHealthNurse有外键联系,所以我最先要删除的是PatientPublicHealthNurse,这样我就通过游标作了一个二级的循环,但运行是系统提示“declare PatientReferralInfo_tb cursor local for select PatientReferralInfoId from PatientReferralInfo where Patientid = @patientid”已经定义过了,其实我也知道在主循环中定义它不对,但刚开始用游标又不知道该怎么做,所以请大家帮我看看怎么做?
还有像我这样的需要,有没有其他的方法作?
谢谢!!!
as
set nocount on
declare @patientid int
declare @PatientReferralInfoId int
declare @PatientReferralInfo_count intdeclare tb cursor local for select patientid from Patient
--我在这里定义了一个主循环的游标
open tb
fetch tb into @patientid
while @@fetch_status = 0
begin
select @PatientReferralInfo_count = count(*) from PatientReferralInfo where Patientid = @patientid
if(@PatientReferralInfo_count>0)
begin
declare PatientReferralInfo_tb cursor local for select PatientReferralInfoId from PatientReferralInfo where Patientid = @patientid
-- 在这里定义了一个主循环游标中的游标
open PatientReferralInfo_tb
fetch PatientReferralInfo_tb into @PatientReferralInfoId
while(@PatientReferralInfo_count>0)
begin
delete PatientPublicHealthNurse where PatientReferralInfoId=@PatientReferralInfoId
set @PatientReferralInfo_count = @PatientReferralInfo_count-1
fetch PatientReferralInfo_tb into @PatientReferralInfoId
-- 附下一个值
end
end
delete PatientReferralInfo where patientid = @patientid
delete patient where PatientId = @patientid
fetch tb into @patientid
-- 附下一个值
end这个存储过程是用在删除数据表Patient中的记录,但是它与表PatientReferralInfo通过外键有联系,所以我要先删除PatientReferralInfo中的记录,但是PatientReferralInfo又与PatientPublicHealthNurse有外键联系,所以我最先要删除的是PatientPublicHealthNurse,这样我就通过游标作了一个二级的循环,但运行是系统提示“declare PatientReferralInfo_tb cursor local for select PatientReferralInfoId from PatientReferralInfo where Patientid = @patientid”已经定义过了,其实我也知道在主循环中定义它不对,但刚开始用游标又不知道该怎么做,所以请大家帮我看看怎么做?
还有像我这样的需要,有没有其他的方法作?
谢谢!!!
Patient表的处理可以影响到另外的两个表。
ALTER proc [dbo].[DeletDuplicatePatient]
as
set nocount on
declare @patientid int
declare @PatientReferralInfoId int
declare @PatientReferralInfo_count intdeclare tb cursor local for select patientid from Patient
--我在这里定义了一个主循环的游标
open tb
fetch tb into @patientid
while @@fetch_status = 0
begin
select @PatientReferralInfo_count = count(*) from PatientReferralInfo where Patientid = @patientid
if(@PatientReferralInfo_count>0)
begin
declare PatientReferralInfo_tb cursor local
for
select PatientReferralInfoId from PatientReferralInfo where Patientid = @patientid
-- 在这里定义了一个主循环游标中的游标
open PatientReferralInfo_tb
fetch PatientReferralInfo_tb into @PatientReferralInfoId
while(@PatientReferralInfo_count>0)
begin
delete PatientPublicHealthNurse where PatientReferralInfoId=@PatientReferralInfoId
set @PatientReferralInfo_count = @PatientReferralInfo_count-1
fetch PatientReferralInfo_tb into @PatientReferralInfoId
-- 附下一个值
end
close PatientReferralInfo_tb
deallocate PatientReferralInfo_tb
end
delete PatientReferralInfo where patientid = @patientid
delete patient where PatientId = @patientid
fetch tb into @patientid
-- 附下一个值
end
close tb
deallocate tb
go--还可以使用级联删除相关字段关系来完成.
Patient表的处理可以影响到另外的两个表。不是那样设置的,只能按特定的删除顺序删除!