question

missingpointer avatar image
missingpointer asked

Guidance on my cursor implementation in SQL 2014

Hi SQL experts,

Currently I have cursor implementation on my stored procedure to execute several stored procedures so loop on each of them to be executed. So far for me this is the best way, but I think the sql experts here can suggest better and more efficient way in terms of performance and maintainability. because so far from what I read cursor is not a good idea in almost many cases or scenario and I'm not sure if this is one of the case like that. I will really appreciate your guidance about it. Thanks in advance!

declare _rulescursor cursorfor

select sprocName

from rulestablename

declare _headercursor cursorfor

select headerid

from #headertemptable

where not headerid isnull

open _rulescursor

fetch next from _rulescursor into @SprocName

while @exceptionsoccurred = 0

and@@fetch_status= 0

begin

open _headercursor

fetchnextfrom _headercursor into @headerid

while @exceptionsoccurred = 0

and@@fetch_status= 0

BEGIN TRY

--will execute stored procedure

exec sp_executesql

@statement=@sprocplusparam,

@params=N'@headerid int',

@hxencounterheaderid=@headerid

END TRY

BEGIN CATCH

select @exceptionsoccurred = 1

insertinto logtable

END CATCH;

fetch next from _headercursor into @headerid

end

close _headercursor

fetch next from _rulescursor into @Sproc

close _rulescursor

deallocate _rulescursor

deallocate _headercursor

t-sqlsql server 2014
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.