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
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