CREATE proc P_fsweb2_WeiBo_InsWeiBoTask
AS
BEGIN
declare @count1 int,@count2 int,@countT int,@countSum int = 0
select @count1 = COUNT(1) from T_WeiBo_Task with(nolock) where servername = 'Local144' and Status is null
select @count2 = COUNT(1) from T_WeiBo_Task with(nolock) where servername = 'AliP001' and Status is null
if(@count1<10)
begin
set @countSum = @countSum +(50-@count1)
end
if(@count2<10)
begin
set @countSum = @countSum +(50-@count2)
end
create table #T_WeiBo_Task(id int)
insert into #T_WeiBo_Task(id)
select top(@countSum ) r.id from 任务队列1 r with(nolock)
join P p with(nolock)
on r.id = p.id
select @countT = COUNT(1) from #T_WeiBo_Task
if(@countT <@countSum )
begin
insert into #T_WeiBo_Task(id)
select top(@countSum - @countT ) r.taskId from 任务队列2 r with(nolock)
join P p with(nolock)
on r.taskId = p.id
end
if(@count1<10)
begin
delete top(50-@count1)
#T_WeiBo_Task
output deleted.id ,'Local144'
into T_WeiBo_Task(t_id,ServerName )
end
if(@count2<10)
begin
delete top(50-@count2)
#T_WeiBo_Task
output deleted.id ,'AliP001'
into T_WeiBo_Task(t_id,ServerName )
end
delete from 任务队列1 where id in(select id from #T_WeiBo_Task )
delete from 任务队列2 where taskId in(select id from #T_WeiBo_Task )
END
如果servername增加的话，我需要改动存储过程，可不可以简化一下存储过程，使改动的地方最小
------解决方案--------------------
要想以后改动小，那你首先的把两句select改成按servername去group by算count，结果丢临时表或者表变量里，然后去用循环滚这几条数据完成后面的操作
------解决方案--------------------
declare @count int,@countT int,@countSum int = 0
将 'Local144' 'AliP001' 都放入到一个临时表中，采用游标从这个临时表遍历
select @count = COUNT(1) from T_WeiBo_Task with(nolock) where servername =@temp and Status is null
if(@count<10)
begin
set @countSum = @countSum +(50-@count)
end
找到了就退出游标循环