今天写了一个游标，但是不知道怎么在这游标里定义一个变量来记录执行时影响条数，并插入两条数据时就跳出循环结束游标。
没有想出辙，实属头疼。附代码如下：
--游标
declare @userID uniqueidentifier--userid
declare y_curr cursor for
select top 2 userID from Hope_Users_db.dbo.u_Users order by addTime desc
open y_curr --打开游标
fetch next from y_curr into @userID ----开始循环游标变量
while(@@fetch_status=0)---返回被FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
begin
if exists (select * from Q_enterprise where userID = @userID)
begin
print @userID
end
else
begin
print @userID
insert into Q_enterprise
select distinct u.userID, u.userType,u.SyncCharityId as CharityId,u.userTrueName,u.userLogo,0 as donationAmount,e.enterpriseContactMobile,
e.enterprisePurpose,0 as enterpriseLongitude,0 as enterpriseLatitude,'' as Infoproportion,0 as sharesum,0 as praisesum,
0 as Donationsum,0 as Farvritessum,0 as Donationsum30,u.addTime,'' as updatetime
from Hope_Users_db.dbo.u_Users u
inner join Hope_Donation_DB.dbo.d_Donation d
on u.userID=d.userID
inner join Hope_Users_db.dbo.u_Enterprise e
on u.userID = e.userID
where u.userID=@userID
end
fetch next from y_curr into @userID --开始循环游标变量
end
close y_curr--关闭游标
deallocate y_curr --释放游标
go
游标实例二：循环读取数据表并进行各种计算并添加数据到临时表，结果输出临时表
USE [CenterDB] GO /****** Object: StoredProcedure [dbo].[Pro_GetSalary] Script Date: 2017/5/23 9:26:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --读取工单详细,并计算每个工单机长的提成 ALTER PROCEDURE [dbo].[Pro_GetSalary] (@sTime nvarchar(255),--查询工单EndTime的开始时间节点 @eTime nvarchar(255),--查询工单EndTime的结束时间节点 @pageIndex int,--开始页码 @pageSize int, --结束页码 @recordCount int output --总记录数 ) AS BEGIN declare @FinishEName nvarchar(40),--操作人名称 @DevName varchar(100),--设备名称 @PrtTotalPageCount int,--单手印数 @PrtSumCount2 int,--总印数 @EndTime DateTime,--结束时间 @ProductName varchar(1202),--印件名称 @knifeCount float,--刀数 @KnifeMoney float,--切刀的提成 @setCount float, --套数 @setMoney float,--印数的提成 @kaiDu nvarchar(255), --开度 @baiNumber int, --百位数字 @PrtPCount int, --正数（名片张数） @DevGroupName nvarchar(255),--机组 @boxNumber int,--名片盒数 @ShouShu varchar(50),--手数 @MPStyle varchar(50),--拼版方式 @strSql nvarchar(max), --拼接sql @startNumber int,--开始页码 @endNumber int --结束页码 --BEGIN TRAN --声明工单事务 set @strSql = 'declare pNumber_cursor CURSOR FOR ' set @strSql = @strSql + ' select A.FinishEName,A.DevName,A.PrtTotalPageCount,A.PrtSumCount2,A.EndTime,A.ProductName,A.PrtPCount,A.ShouShu,A.MPStyle,B.DevGroupName,B.KaiDu'; set @strSql = @strSql +' from QuePlanArrage as A inner join DevGroup B on A.DevName = B.DevName where 1=1'; if @sTime != '' and @eTime != '' begin set @strSql = @strSql +' and A.EndTime between cast('''+@sTime+' 00:00:00.000'' as datetime) and cast('''+@eTime+' 23:59:59.999'' as datetime)'; end if @sTime != '' and @eTime = '' begin set @strSql = @strSql +' and A.EndTime >= cast('''+@sTime+' 00:00:00.000'' as datetime)'; end if @sTime = '' and @eTime != '' begin set @strSql = @strSql +' and A.EndTime <= cast('''+@eTime+' 23:59:59.999'' as datetime)'; end set @strSql = @strSql + ' and A.State=13 and (A.JobID=''9'' or A.JobID=''10'') Order by A.DevName,A.ID' ; EXEC sp_executesql @strSql --新建临时表 if object_id(N'##SalaryInfo',N'U') is not null begin drop table ##SalaryInfo end create table ##SalaryInfo ( ID int identity(1,1), FinishEName nvarchar(40), DevName varchar(100) null, PrtTotalPageCount int, PrtSumCount2 int, EndTime DateTime, ProductName varchar(1202), SetNumber int, KnifeMoney Decimal(12,1), setMoney Decimal(12,1), Salary Decimal(12,1) ) --打开游标 open pNumber_cursor WHILE @@FETCH_STATUS = 0 --返回被FETCH语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。 begin --开始循环游标变量 FETCH NEXT FROM pNumber_cursor INTO @FinishEName,@DevName,@PrtTotalPageCount,@PrtSumCount2,@EndTime,@ProductName,@PrtPCount,@ShouShu,@MPStyle,@DevGroupName,@KaiDu if @DevName!=null or @DevName != ''--说明当前行有数据 begin --执行sql操作 --计算总印数结束 --根据总印数计算套数开始 set @baiNumber = @PrtSumCount2/100%10; if @baiNumber <2 begin set @PrtSumCount2 = cast((cast(@PrtSumCount2/1000 as varchar)+'000') as int); end if @PrtSumCount2 <3300 begin set @setCount = 1; end else begin set @setCount = round(cast(@PrtSumCount2 as float)/cast(3000 as float),1); end --根据总印数计算套数结束 --根据套数计算提成开始 if @DevGroupName = 'AAAAA' begin set @setMoney = @setCount * 17; end if @DevGroupName = 'BBBBB' begin if CHARINDEX('名片',@ProductName)>0 begin set @setMoney = @setCount *16; end else begin set @setMoney = @setCount *23; end end if @DevGroupName ='CCCCC' begin set @setMoney = @setCount*13; end if @DevGroupName ='DDDDD' begin set @setMoney = @setCount*17; end if @DevGroupName ='EEEEE' begin set @setMoney = @setCount*19.5; end --根据套数计算提成结束 if charindex('不干胶',@ProductName)>0 --类别为不干胶 Begin declare @exKnife int --基数--转换刀数 if @kaiDu = '八开' begin set @exKnife = 1; end else if @kaiDu = '四开' begin set @exKnife = 2; end else begin set @exKnife = 3; end if @PrtSumCount2%500 > 50 begin set @knifeCount =(@PrtSumCount2/500+1)*@exKnife; end else begin set @knifeCount = (@PrtSumCount2/500)*@exknife; end set @KnifeMoney = @knifeCount*1.9; End Else if CHARINDEX('名片',@productName)>0 --类别为名片 Begin set @boxNumber = @PrtPCount/100; if @boxNumber <2.5 begin set @knifeCount = 1; end else begin set @knifeCount = @boxNumber/2.5; end set @KnifeMoney = @knifeCount*4.2; End Else --类别为彩页 Begin declare @baseNumber int, @exchangeKnife int --基数--转换刀数 if @kaiDu='全开' begin set @baseNumber = 1000; set @exchangeKnife = 3; end if @kaiDu ='对开' begin set @baseNumber=1000; set @exchangeKnife = 1; end if @kaiDu ='四开' begin set @baseNumber = 2000; set @exchangeKnife = 1; end if @kaiDu ='八开' begin set @baseNumber = 2500; set @exchangeKnife = 1; end if @kaiDu ='十六开' begin set @baseNumber = 5000; set @exchangeKnife = 1; end if @kaiDu ='三十二开及其以上' begin set @baseNumber = 10000; set @exchangeKnife = 1; end --彩页：开始计算刀数 if @PrtSumCount2 < @baseNumber begin set @knifeCount=1*@exchangeKnife end else if @PrtSumCount2%@baseNumber>100 begin set @knifeCount=(@PrtSumCount2/@baseNumber+1)*@exchangeKnife end else begin set @knifeCount = (@PrtSumCount2/@baseNumber)*@exchangeKnife end set @KnifeMoney = @knifeCount*2; End --刀数和套数均已有结果 --判断是否已有临时表，无则新建添加有则添加 insert into ##SalaryInfo (FinishEName,DevName,PrtTotalPageCount,PrtSumCount2,EndTime,ProductName,SetNumber,KnifeMoney,setMoney,Salary) values(@FinishEName,@DevName,@PrtTotalPageCount,@PrtSumCount2,@EndTime,@ProductName,@setCount,@KnifeMoney,@setMoney,(@KnifeMoney+@setMoney)) end end --分页代码 if @pageIndex = 1 begin set @startNumber = 1; end else begin set @startNumber = (@PageIndex-1)*@pageSize + 1; end set @endNumber = @pageIndex*@pageSize; --计算总记录数 DECLARE @strCountSql NVARCHAR(1000) SET @strCountSql = 'Select @reCount = count(1) FROM (select FinishEName,DevName from ##SalaryInfo group by FinishEName,DevName) A' EXEC sp_executesql @strCountSql, N'@reCount int OUTPUT', @recordCount OUTPUT --select FinishEName,DevName,SUM(SetNumber) as SetNumberSum,SUM(PrtTotalPageCount) as PrtTotalPageCountSum,SUM(PrtSumCount2) as PrtSumCount2Sum,SUM(KnifeMoney) as KnifeMoneySum,SUM(setMoney) as setMoneySum,SUM(Salary) as SalarySum --from ##SalaryInfo --group by FinishEName,DevName select * from (select ROW_NUMBER() over(order by DevName) as rownum,FinishEName,DevName,SUM(SetNumber) as SetNumberSum,SUM(PrtTotalPageCount) as PrtTotalPageCountSum,SUM(PrtSumCount2) as PrtSumCount2Sum,SUM(KnifeMoney) as KnifeMoneySum,SUM(setMoney) as setMoneySum,SUM(Salary) as SalarySum from ##SalaryInfo where 1=1 group by FinishEName,DevName) AS D where rownum between @startNumber and @endNumber CLOSE pNumber_cursor--关闭游标 DEALLOCATE pNumber_cursor--释放游标 --删除临时表 drop table ##SalaryInfo END GO