Сохраненная процедура без курсоров

Как я могу написать следующий sp без курсора ?. Более того, это не дает мне желаемого результата. Я не писал этого, я пытаюсь понять, что с этим не так.

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 

Следующий оператор select из приведенного выше sp дает мне правильный набор данных, но курсор, который проходит через меня, дает мне другой результат.

 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 ) 

 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 в 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