下面这个存储过程能不能再优化一下啊 ?
CREATE PROCEDURE peoplechange(@database1 varchar(50),@database2 varchar(50),@tablename2 varchar(50))
AS
begin
declare @sqlStr varchar(5000)
declare @oldBaseName varchar(50)
declare @newBaseName varchar(50)
declare @TableName varchar(50)
set @oldBaseName= @database1
set @newBaseName=@database2
set @TableName=@tablename2 begin
-----删除深圳活动表
while @TableName='活动表'
begin
select @sqlStr ='delete a from '+@oldBaseName+'..活动表 as a '
select @sqlStr =@sqlStr +'
where exists(select 1 from '+@newBaseName+'..活动表 as b
where a.人员编号 = b.人员编号 and a.内容 = b.内容 and a.类别 = b.类别 and
a.学科 = b.学科 and a.形式 = b.形式 and a.学分 = b.学分 and a.学时 = b.学时 and
a.日期 = b.日期) '
exec (@sqlStr)
break
endend
end
CREATE PROCEDURE peoplechange(@database1 varchar(50),@database2 varchar(50),@tablename2 varchar(50))
AS
begin
declare @sqlStr varchar(5000)
declare @oldBaseName varchar(50)
declare @newBaseName varchar(50)
declare @TableName varchar(50)
set @oldBaseName= @database1
set @newBaseName=@database2
set @TableName=@tablename2 begin
-----删除深圳活动表
while @TableName='活动表'
begin
select @sqlStr ='delete a from '+@oldBaseName+'..活动表 as a '
select @sqlStr =@sqlStr +'
where exists(select 1 from '+@newBaseName+'..活动表 as b
where a.人员编号 = b.人员编号 and a.内容 = b.内容 and a.类别 = b.类别 and
a.学科 = b.学科 and a.形式 = b.形式 and a.学分 = b.学分 and a.学时 = b.学时 and
a.日期 = b.日期) '
exec (@sqlStr)
break
endend
end
select @sqlStr ='delete a from '+@oldBaseName+'..活动表 as a
join '+@newBaseName+'..活动表 as b
on a.人员编号 = b.人员编号 and a.内容 = b.内容 and a.类别 = b.类别 and
a.学科 = b.学科 and a.形式 = b.形式 and a.学分 = b.学分 and a.学时 = b.学时 and
a.日期 = b.日期 '