问题描述
可能的重复:
没有游标的存储过程
如何在没有光标的情况下编写以下sp?.更多的是它没有给我想要的输出.这不是我写的,我是想解释这有什么问题.
How can I write the following sp without the cursor?. More over its not giving me the desired output. I didn't write this, I am trying to interpret what is wrong with this.
ALTER PROCEDURE [dbo].[AccreditationExpiryCheck] AS BEGIN SET NOCOUNT ON; declare @taskTypeId int = 19 -- Accreditations, automated declare @firstActionTypeId int = 23 -- Accreditation expiring declare @nextActionTypeId int = 3 -- Call company declare @companyId int declare @accreditationId int declare @comment nvarchar(max) = N' accreditation for this company has expired.' -- find all companies and accreditations expiring declare companies cursor local forward_only read_only for select c.Company_Id, a.Accred_ID from COMPANY c inner join MEMBERSHIP m on c.Company_ID = m.Company_ID inner join ACCREDITATION a on c.Company_ID = a.Company_ID where -- Accreditation expired yesterday cast(a.Accred_ExpDate as DATE) = cast(DATEADD(DAY, -1, GETDATE()) as DATE) and m.IsMember_Ind = 1 and (c.HQ_ID IS NULL OR c.HQ_ID = c.Company_ID) -- FB4640: this isn't a 'team' co (with an HQ) -- and there is no action of this type created within 1 day -- of the expiry date and not exists ( select * from TaskAction ta where ta.FirstActionTypeId = @firstActionTypeId and ta.TaskTypeId = @taskTypeId and ta.TaskCreatedOn BETWEEN a.Accred_ExpDate AND DATEADD(DAY, 1, a.Accred_ExpDate) and ta.EntityId = c.Company_ID and ta.EntityTypeId = 1 ) open companies fetch next from companies into @companyId, @accreditationId declare @title nvarchar(max) = (select AccredType_Name from ACCREDITATION_TYPE at inner join ACCREDITATION a on at.AccredType_ID = a.AccredType_ID where a.Accred_ID = @accreditationId) declare @comment2 nvarchar(max) = isnull(@title, '') + ' accreditation for this company has expired.' while @@FETCH_STATUS = 0 begin exec CreateSystemTask @taskTypeId, @firstActionTypeId, @nextActionTypeId, @companyid, @comment2, @title fetch next from companies into @companyId,@accreditationId end close companies deallocate companies END
来自上述 sp 的以下 select 语句为我提供了正确的数据集,但循环遍历的光标为我提供了不同的输出.
The following select statement from the above sp gives me the correct dataset, but the cursor which loops through gives me a different output.
select c.Company_Id, a.Accred_ID from COMPANY c inner join MEMBERSHIP m on c.Company_ID = m.Company_ID inner join ACCREDITATION a on c.Company_ID = a.Company_ID where -- Accreditation expired yesterday cast(a.Accred_ExpDate as DATE) = cast(DATEADD(DAY, -1, GETDATE()) as DATE) and m.IsMember_Ind = 1 and (c.HQ_ID IS NULL OR c.HQ_ID = c.Company_ID) -- FB4640: this isn't a 'team' co (with an HQ) -- and there is no action of this type created within 1 day -- of the expiry date and not exists ( select * from TaskAction ta where ta.FirstActionTypeId = @firstActionTypeId and ta.TaskTypeId = @taskTypeId and ta.TaskCreatedOn BETWEEN a.Accred_ExpDate AND DATEADD(DAY, 1, a.Accred_ExpDate) and ta.EntityId = c.Company_ID and ta.EntityTypeId = 1 )
推荐答案
您可以使用 while 循环摆脱光标,如下图所示.如果您查询的数据包含一个唯一标识符,您可以取消临时表,但在您的示例中,我使用了一个临时表并包含了一个 recordId.
You can get rid of the cursor with a while loop as illustrated below. If the data you are querying included a unique identifier you could do away with the temp table but for your example I used a temp table and included a recordId.
ALTER PROCEDURE [dbo].[AccreditationExpiryCheck] AS BEGIN SET NOCOUNT ON; declare @taskTypeId int = 19 -- Accreditations, automated declare @firstActionTypeId int = 23 -- Accreditation expiring declare @nextActionTypeId int = 3 -- Call company declare @companyId int declare @accreditationId int declare @comment nvarchar(max) = N' accreditation for this company has expired.' -- find all companies and accreditations expiring select ROW_NUMBER() OVER(ORDER BY c.Company_Id, a.Accred_ID) as [RecordId], c.Company_Id as [Company_Id], a.Accred_ID as [Accred_ID] into #COMPANIES from COMPANY c inner join MEMBERSHIP m on c.Company_ID = m.Company_ID inner join ACCREDITATION a on c.Company_ID = a.Company_ID where -- Accreditation expired yesterday cast(a.Accred_ExpDate as DATE) = cast(DATEADD(DAY, -1, GETDATE()) as DATE) and m.IsMember_Ind = 1 and (c.HQ_ID IS NULL OR c.HQ_ID = c.Company_ID) -- FB4640: this isn't a 'team' co (with an HQ) -- and there is no action of this type created within 1 day -- of the expiry date and not exists ( select * from TaskAction ta where ta.FirstActionTypeId = @firstActionTypeId and ta.TaskTypeId = @taskTypeId and ta.TaskCreatedOn BETWEEN a.Accred_ExpDate AND DATEADD(DAY, 1, a.Accred_ExpDate) and ta.EntityId = c.Company_ID and ta.EntityTypeId = 1 ) declare @recordId int = 0; declare @title nvarchar(max); declare @comment2 nvarchar(max); while(1=1) begin select top 1 @recordId = [RecordId] ,@companyId = [CompanyId] ,@accreditationId = [Accred_ID] from #COMPANIES where [RecordId] > @recordId if @@ROWCOUNT = 0 break; set @title = (select AccredType_Name from ACCREDITATION_TYPE at inner join ACCREDITATION a on at.AccredType_ID = a.AccredType_ID where a.Accred_ID = @accreditationId) set @comment2 = isnull(@title, '') + ' accreditation for this company has expired.' exec CreateSystemTask @taskTypeId, @firstActionTypeId, @nextActionTypeId, @companyid, @comment2, @title end drop table #COMPANIES END