没有游标的存储过程 [英] Stored procedure without cursors
本文介绍了没有游标的存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在没有光标的情况下编写以下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 )
推荐答案
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
这篇关于没有游标的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文