@@FETCH_STATUS problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

@@FETCH_STATUS problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-10 : 07:50:55

szymex writes "Hi,

I have problem with @@FETCH_STATUS global variable, I'm trying to make sych a code:

DECLARE crRecPzw CURSOR
FOR SELECT col1, col2 FROM table
OPEN crRecPzw
FETCH NEXT FROM crRecPzw INTO @col1, @col2
WHILE @@FETCH_STATUS = 0
....
and here is a loop, I would like to put another cursor here with loop but in this case there will be a problem with @@FETCH_STATUS which is global and I will not be able to continue first loop.

Could you suggest me how to avoid this problem?

Szymon"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-10-10 : 07:51:16

Certainly, never use cursors.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 09:02:29

Post your exact requirement
There may be better approach than Cursors

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-10 : 09:22:24

quote:
Originally posted by robvolk

Certainly, never use cursors.

Of Course the obvious answer...BUT, you need to save the status to a local variable and reference that, instead of @@FETCH_STATUS

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-10-10 : 14:35:49

Try something like this:


declare My_Cursor cursor local
for
select
	a.MyCol
from
	MyTable a
order by
	a.MyCol

open My_Cursor

declare @fetch_status	int
declare @MyCol		varchar(100)

select @fetch_status = 0

while @fetch_status = 0
	begin

	fetch next from My_Cursor
	into	@MyCol

	select @fetch_status = @@fetch_status

	if @fetch_status <> 0
		begin
		-- return to beginning of while loop
		continue
		end

	-- Do some stuff here

	end --While end

close My_Cursor

deallocate My_Cursor

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources