question

venky525 avatar image
venky525 asked

How to replace below cursor code to any other method

CREATE PROCEDURE [dbo].[REQ_HEADER_PROC_ALL] AS

BEGIN


DECLARE @id CURSOR;

declare @pkey int;


SET @id = CURSOR FOR SELECT id FROM REQ_HEADER

OPEN @id

FETCH NEXT FROM @id INTO @pkey


WHILE @@FETCH_STATUS = 0

BEGIN

exec REQ_HEADER_proc @pkey

FETCH NEXT FROM @id INTO @pkey

END;


CLOSE @id;

DEALLOCATE @id;

END

tsqlsqlserver
1 comment
10 |1200

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

It rather depends on what REQ_HEADER is doing...

0 Likes 0 ·

1 Answer

·
Jeff Moden avatar image
Jeff Moden answered

It would appear that the REQ_HEADER_proc only accepts one row at a time in a very RBAR fashion. The cursor isn't the problem here. The proc is. It will need to be rewritten to do whatever it does to a set of IDs rather than just one at a time. Replacing this with any other method other than rewriting the proc itself is a total waste of time and effort.

10 |1200

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

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.