Sql cursor not looping - 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 2005 Forums

Transact-SQL (2005)

Sql cursor not looping

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2009-04-14 : 12:44:02

Hi,
I have the following cursor that I am trying to test. However the procedure when tested does not go through the loop. I am not sure why this is not looping. I appreciate any help for resolution of this issue. Thanks

Code:
ALTER PROCEDURE [dbo].[test_release2]
as

declare job_order cursor
for
select ordnum
from tblProjectionold

Declare @ordnum varchar(50),
@msg varchar(150)

open job_order
fetch next from job_order into @ordnum

WHILE @@FETCH_STATUS = 0

select @msg = @ordnum
PRINT @msg
fetch next from job_order into @ordnum
close job_order
deallocate job_order

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-14 : 12:49:15

to solve the cursor problem you need BEGIN/END blocks starting after the "while" and ending after the "fetch next..."

What are you trying to do? You probably don't even need a cursor

Be One with the Optimizer
TG

gamaz
Posting Yak Master

104 Posts

Posted - 2009-04-14 : 12:58:41

Thanks TG for the help. I appreciate it. Actually we have a production schedule reporting system which has stored procedure for processing. Someone built this system few years back. The person used cursor in the processing quite heavily. I have to now modify some of the reports. I do not have time to rewrite the procedures as those require tons of time. Hence I am trying to pick up cursor to make changes in the shortest time. I have avoided cursor and worked on sql procedure without the use of cursor.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-14 : 13:18:37

First of all, is there anything in the table tblProjectionold? If so try this

ALTER PROCEDURE [dbo].[test_release2]
as
Declare @ordnum varchar(50),
@msg varchar(150)

declare job_order cursor
for
select ordnum
from tblProjectionold

open job_order
fetch next from job_order into @ordnum

WHILE @@FETCH_STATUS = 0

BEGIN
PRINT @ordnum
fetch next from job_order into @ordnum
close job_order
END
deallocate job_order

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-14 : 13:22:46

quote:
Originally posted by yosiasz
WHILE @@FETCH_STATUS = 0

BEGIN
PRINT @ordnum
fetch next from job_order into @ordnum
close job_order
END
deallocate job_order

not sure what you were adding beyond my post but I believe you put the END in the wrong place. You will close the cursor at the first itteration.

Be One with the Optimizer
TG

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-14 : 13:28:27

oops sorry about that TG. I was just posting what you stated + moving the declaration of those variables before declare job_order cursor.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources