sqlserver游标循环换成while循环结果集

最新推荐文章于 2024-08-20 16:07:54 发布

a545132569

最新推荐文章于 2024-08-20 16:07:54 发布

阅读量1k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/a545132569/article/details/88616775

版权

数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

if(exists (select * from sys.objects where name = 'procName'))
drop proc procName
go
Create proc [dbo].[procName](@resultId int)
AS
DECLARE @fact int=0,@other int=0,@asId int=0,@code varchar(20)='',@code1 varchar(20)='',@objCode varchar(20)='',@title nvarchar(255)='',@contract nvarchar(255)=''

   set @objCode=(select so.objCode from Result r join SurveyObj so on r.surveyObjId=so.surveyObjId where r.resultId = @resultId )
   declare @cid int=0
   select top 1 @cid = cid from CounterMap order by cid
   while @cid is not null
   begin
       --用查询的方式赋值
       select @asId=answerId,@code=counterCode,@code1=counterCode1,@title=answerTitle from CounterMap where cid=@cid

       set @fact=(select result from ResultDtl where answerId = @asId and questionId=21700 and resultId = @resultId)
       set @other=(select sum(hardCoreNum) from hardcore_HW where objCode = @objCode and hardCoreCode in(select str2table from StrToTable(@contract)))
       --原理是每一次取最上面的一条数据进行循环直到结束
       set @cid = (
           select top 1 cid from CounterMap where cid>@cid order by cid
       )
   end