新建存储过程 但好像不执行sql2008环境 内容是从a表取出数据循环存入b表
USE [CallCenter_2012]
GO
/****** Object: StoredProcedure [dbo].[x_p_Statistics_Today] Script Date: 06/06/2012 08:50:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[x_p_Statistics_Today]
@workercallerid varchar(30)=NULL,
@oncallhold int=null,
@hold int=null,
@count int=NULL
as
begin
declare x_Stat cursor
--查询
for select workercallerid,count(1) as 'count',sum(oncallhold) as 'oncallhold' ,sum(hold) as 'hold' from dbo.t_WorkerOnCallLog_T group by workercallerid
--打开游标
open x_Stat
--检索一条数据
fetch next from x_Stat into @workercallerid,@count,@oncallhold,@hold
--循环开始
while (@@FETCH_STATUS=0)
begin
insert into dbo.x_Statistics (EmployeeId,EffectiveNumRule,EffectiveTimeRule,EffectiveNum,EffectiveTime,CreateTime) VALUES (@workercallerid,'','',@count,@hold,GETDATE())
--下一个循环
fetch next from x_Stat into @workercallerid,@count,@oncallhold,@hold
end
close x_Stat
deallocate x_Stat
end
------解决方案--------------------
改成这样试试：
- SQL code
insert into dbo.x_Statistics (EmployeeId,EffectiveNumRule,EffectiveTimeRule,EffectiveNum,EffectiveTime,CreateTime)select workercallerid,'','',count,hold,getdate() from(select workercallerid,count(1) as 'count',sum(oncallhold) as 'oncallhold' ,sum(hold) as 'hold' from dbo.t_WorkerOnCallLog_T group by workercallerid) as a1