Cursor Alternative in SQL

Question

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

dotnet-tricks.com/Tutorial/sqlserver/…

mohan111
– mohan111

2015-04-09 15:53:29 +00:00
Commented Apr 9, 2015 at 15:53 — mohan111
– mohan111, Commented Apr 9, 2015 at 15:53

Sherman · Accepted Answer · 2015-04-09 16:23:58Z

2

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

edited Apr 9, 2015 at 16:23

answered Apr 9, 2015 at 16:18

Sherman

8535 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TavoloPerUno · Accepted Answer · 2015-04-09 16:36:48Z

0

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?

answered Apr 9, 2015 at 16:36

TavoloPerUno

5792 silver badges7 bronze badges

1 Comment

benjamin moskovits Over a year ago

For 419 rows the big user of time is the exec statement - changing it to a non-cursor will very likely not speed up your execution time. Not using a cursor is a very good habit though.

Collectives™ on Stack Overflow

Cursor Alternative in SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related