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.
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. ThanksCode:ALTER PROCEDURE [dbo].[test_release2]as declare job_order cursor forselect ordnumfrom tblProjectionoldDeclare @ordnum varchar(50),@msg varchar(150)open job_orderfetch next from job_order into @ordnumWHILE @@FETCH_STATUS = 0select @msg = @ordnum PRINT @msgfetch next from job_order into @ordnumclose job_orderdeallocate 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 cursorBe One with the OptimizerTG |
 |
|
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 thisALTER PROCEDURE [dbo].[test_release2]as Declare @ordnum varchar(50),@msg varchar(150)declare job_order cursor forselect ordnumfrom tblProjectionoldopen job_orderfetch next from job_order into @ordnumWHILE @@FETCH_STATUS = 0 BEGIN PRINT @ordnum fetch next from job_order into @ordnum close job_order ENDdeallocate 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 yosiaszWHILE @@FETCH_STATUS = 0 BEGIN PRINT @ordnum fetch next from job_order into @ordnum close job_order ENDdeallocate 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 OptimizerTG |
 |
|
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 |
 |
|
|
|
|