create procedure Owner_Distribution_Info(@handleby varchar(6),@total int,@amountcount int)
as
begin
declare @sql varchar(8000),@midvar int
set @sql='update c_dzh_assigninfo
set assigncount='+rtrim(@total/@amountcount)+' where cur_staff
in(select top '+rtrim(@amountcount)+' cur_staff from c_dzh_assigninfo) and handleby='''+@handleby+''''
exec(@sql)
if(rtrim(@total-(@total/@amountcount)*@amountcount)>=1 and @total-(@total/@amountcount)*@amountcount <@amountcount)
begin
set @midvar=floor((@total-(@total/@amountcount)*@amountcount)/@amountcount+1)
end
set @sql='update c_dzh_assigninfo set assigncount=assigncount+'+rtrim(ltrim(@midvar))+' where
cur_staff in(select top '+rtrim(@total-(@total/@amountcount)*@amountcount)+' cur_staff from c_dzh_assigninfo) and handleby='''+@handleby+''''
exec(@sql)
end
as
begin
declare @sql varchar(8000),@midvar int
set @sql='update c_dzh_assigninfo
set assigncount='+rtrim(@total/@amountcount)+' where cur_staff
in(select top '+rtrim(@amountcount)+' cur_staff from c_dzh_assigninfo) and handleby='''+@handleby+''''
exec(@sql)
if(rtrim(@total-(@total/@amountcount)*@amountcount)>=1 and @total-(@total/@amountcount)*@amountcount <@amountcount)
begin
set @midvar=floor((@total-(@total/@amountcount)*@amountcount)/@amountcount+1)
end
set @sql='update c_dzh_assigninfo set assigncount=assigncount+'+rtrim(ltrim(@midvar))+' where
cur_staff in(select top '+rtrim(@total-(@total/@amountcount)*@amountcount)+' cur_staff from c_dzh_assigninfo) and handleby='''+@handleby+''''
exec(@sql)
end
as
begin
declare @sql varchar(8000),@midvar int
set @sql='update c_dzh_assigninfo
set assigncount='+rtrim(@total/@amountcount)+' where cur_staff
in(select top '+rtrim(@amountcount)+' cur_staff from c_dzh_assigninfo) and handleby='+@handleby+''
exec(@sql)
if(rtrim(@total-(@total/@amountcount)*@amountcount)>=1 and @total-(@total/@amountcount)*@amountcount <@amountcount)
begin
set @midvar=floor((@total-(@total/@amountcount)*@amountcount)/@amountcount+1)
end
set @sql='update c_dzh_assigninfo set assigncount=assigncount+'+rtrim(ltrim(@midvar))+' where
cur_staff in(select top '+rtrim(@total-(@total/@amountcount)*@amountcount)+' cur_staff from c_dzh_assigninfo) and handleby='+@handleby+''
exec(@sql)
end
create procedure Owner_Distribution_Info(@handleby varchar(6),@total int,@amountcount int)
as
begin
declare @sql varchar(8000),@midvar int
set @sql='update c_dzh_assigninfo
set assigncount='+rtrim(@total/@amountcount)+' where cur_staff
in(select top '+rtrim(@amountcount)+' cur_staff from c_dzh_assigninfo) and handleby='''+@handleby+''
exec(@sql)
if(rtrim(@total-(@total/@amountcount)*@amountcount)>=1 and @total-(@total/@amountcount)*@amountcount <@amountcount)
begin
set @midvar=floor((@total-(@total/@amountcount)*@amountcount)/@amountcount+1)
end
set @sql='update c_dzh_assigninfo set assigncount=assigncount+'+rtrim(ltrim(@midvar))+' where
cur_staff in(select top '+rtrim(@total-(@total/@amountcount)*@amountcount)+' cur_staff from c_dzh_assigninfo) and handleby='+@handleby+''
exec(@sql)
end然后调用执行该存储过程 执行的语句:
exec Owner_Distribution_Info 'SELITE',14,3
create procedure Owner_Distribution_Info(@handleby varchar(6),@total int,@amountcount int)
as
begin
declare @sql varchar(8000),@midvar int
set @sql='update c_dzh_assigninfo
set assigncount='+rtrim(@total/@amountcount)+' where cur_staff
in(select top '+rtrim(@amountcount)+' cur_staff from c_dzh_assigninfo) and handleby='''+@handleby+''''
exec(@sql)
if(rtrim(@total-(@total/@amountcount)*@amountcount)>=1 and @total-(@total/@amountcount)*@amountcount <@amountcount)
begin
set @midvar=floor((@total-(@total/@amountcount)*@amountcount)/@amountcount+1)
end
set @sql='update c_dzh_assigninfo set assigncount=assigncount+'+rtrim(ltrim(@midvar))+' where
cur_staff in(select top '+rtrim(@total-(@total/@amountcount)*@amountcount)+' cur_staff from c_dzh_assigninfo) and handleby='''+@handleby+''''
exec(@sql)
end然后调用执行该存储过程 执行的语句:
exec Owner_Distribution_Info 'SELITE',14,3