CODEWITHSUNDEEP
sql-serverloopscursor
user3267755
user3267755

Reputation: 1070

Cursor Alternative in SQL

I'm a jr. level developer and am working on a script in SQL to try and iterate over a result set from a select statement and for each row that is returned, take a handful of columns and pass them into a stored procedure. I have put together this script from other examples I've found on StackOverflow, and it's been running for 33 minutes. I did some research and I'm now seeing that cursors should only be used as a last resort. :(

Could somebody help me refactor this into some more performance friendly? The result set that I'm working with has 419 rows in it.

declare @docid uniqueidentifier
declare @publish_xml nvarchar(max) = null
declare @pub_text1 nvarchar(max) = null
declare @pub_text2 nvarchar(max) = 'Physical Mail'
declare @pub_text3 nvarchar(max) = null

declare cur CURSOR LOCAL for
        select d.document_id, d.published_xml, d.published_text1, d.published_text2, d.published_text3 
            from tblCMS_Document d 
            where d.template_id = '357760AD-1D33-4162-A813-20F56901C18D'
open cur

fetch next from cur into @docid, @publish_xml, @pub_text1, @pub_text2, @pub_text3

while @@FETCH_STATUS = 0 BEGIN

    exec [usp_CMS_Document_Publish] @docid, @publish_xml, @pub_text1, 'Physical Mail', @pub_text3

END

CLOSE cur   
DEALLOCATE cur

Upvotes: 0

Views: 223

Answers (2)

TavoloPerUno
TavoloPerUno

Reputation: 579

I would move the cursor query into usp_CMS_Document_Publish and perform this publish operation on a set based approach. Is it not possible to edit usp_CMS_Document_Publish?

Upvotes: 0

Sherman
Sherman

Reputation: 853

This doesn't answer your question but it may be why your cursor is running forever. You are missing a FETCH NEXT inside your while loop:

open cur

fetch next from cur into @docid, @publish_xml, @pub_text1, @pub_text2, @pub_text3

while @@FETCH_STATUS = 0 BEGIN

    exec [usp_CMS_Document_Publish] @docid, @publish_xml, @pub_text1, 'Physical Mail', @pub_text3

    fetch next from cur into @docid, @publish_xml, @pub_text1, @pub_text2, @pub_text3

END

Upvotes: 2

Related Questions