How to Execute a Stored Procedure Over a Set Without Using a Cursor

Execute Store Procedure For All Rows in SELECT without using Cursor

You only have a couple of choices. Either modify your procedure to receive a table valued function so you can deal with it set based. Or you are stuck doing some sort of looping, most likely a cursor.

Writing the stored procedure without cursor

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 

SQL Server : call stored procedure with side effects for each row without using a cursor

cursors are inherently bad

No. Cursors are easily misused, and tend to be leapt upon by people new to SQL because they're from a procedural background and haven't even heard of "set-based". Cursors have their place, and if you've assessed the available approaches and concluded that cursors are a good fit, I'd say use one.

Using a WHILE loop to hide the fact that what you're really doing is using a cursor is also something I'd recommend against.

One final note - you mentioned fast_forward and read_only - but one other recommendation would be local - that way, if something goes wrong, at least the cursor is cleaned up when you exit whatever scope your cursor is running in - rather than persisting for the lifetime of the connection.

Call stored procedure for each XML element without cursors

alter PROCEDURE [dbo].[spSendLogLinesAsXML]
(
    @device_id varchar(128),
    @application_name VARCHAR(64),
    @application_user_name VARCHAR(6),
    @log_lines_xml XML
)
AS
BEGIN

declare @log_line_dt datetime,
        @log_line_message varchar(1024)

declare @curse cursor 
set @curse = cursor fast_forward for 
select n.d.value('dt[1]', 'datetime') as log_line_dt, n.d.value('data[1]', 'varchar(1024)') as log_line_message
from @log_lines_xml.nodes('/lines/line') n(d)

open @curse

fetch next from @curse into @log_line_dt, @log_line_message

while (@@fetch_status = 0)
begin
    EXEC spSendLogLine
        @device_id = @device_id,
        @application_name = @application_name,
        @application_user_name = @application_user_name,
        @log_line_dt = @log_line_dt,
        @log_line_message = @log_line_message   

    fetch next from @curse into @log_line_dt, @log_line_message
end

close @curse;

return -100
END
GO

---

Related Topics