当前位置: 代码迷 >> Sql Server >> sql游标有关问题求解
  详细解决方案

sql游标有关问题求解

热度:83   发布时间:2016-04-27 12:24:55.0
sql游标问题求解
新建存储过程 但好像不执行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
  相关解决方案