--创建一个存储过程,并返回一个已经打开的游标
Create procedure return_cursor
(
@userId varchar(40),
@Cur_KPI_Guid CURSOR VARYING OUTPUT
)
AS
Begin
set @Cur_KPI_Guid=Cursor Local Scroll For
select Guid from KPI_User where UserID=@userId
open @Cur_KPI_Guid
end
--求和的存储过程
create procedure Sum_num
(
@userID varchar(40)
)
AS
Declare
@Cur_KPI_Guid CURSOR,--游标变量
@KPI_Guid varchar(50)
BEGIN
--调用上面的存储过程,得到打开的游标
execute return_cursor @userID,@Cur_KPI_Guid OUTPUT
--取出游标中的第一行数据
FETCH @Cur_KPI_Guid INTO @KPI_Guid
--循环计算每个表的总分
While @@FETCH_STATUS=0
begin
select SUM(Num) from KPI_UserNumItem where ItemGuid
in (select ItemGuid from KPI_BaseItems where Guid=@KPI_