問題描述

可能的重復:
沒有游標的存儲過程

如何在沒有光標的情況下編寫以下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 

這篇關于編寫沒有游標的存儲過程的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！