Combining Cursor and While Loop

Hi all

I have the code below.
I need to count the number of rows in my table
Using the countvariable.. I will performe cursor.. The number of times the countvariable returns..
At the same time I will use the numbers as a variable in a where condition inside the cursor..
is this possible.. if yes..
Can you help me position the cursor.. I am getting multiple rows return.. and the variable assigment inside the cursor is not working.

Thanks in Advance


DECLARE @Flag INT,
		@counterCheck int,
		@PositionNr INT
		
 SET @Flag = 0
 SET @PositionNr = 1
 SET @counterCheck = (SELECT COUNT(*) FROM myTransaction WHERE TransactionID = 9010141)
 -- Get the number of Position in MyTransaction
 
 WHILE (@Flag < @counterCheck)
 BEGIN
		 BEGIN
			PRINT @Flag

					SET NOCOUNT ON;

				DECLARE @ColExpir datetime
				DECLARE @ColFallprotec datetime
				DECLARE @ColWorkid int
				--------------------------------------------------------
				DECLARE @MyCursor CURSOR
				SET @MyCursor = CURSOR FAST_FORWARD
				FOR
				SELECT Table_Training_Detalis.DateExpires,Table_Training_Detalis.Worker_ID
				FROM   Table_Courses 
				-- Use the PositionNr as a variable
				WHERE  PositionNr = @PositionNr
				
				
				OPEN @MyCursor
				FETCH NEXT FROM @MyCursor
				INTO @ColExpir,@ColWorkid
				WHILE @@FETCH_STATUS = 0
				BEGIN
				update Table_Workers set WHIMIS= @ColExpir where Worker_ID=@ColWorkid
				 
				FETCH NEXT FROM @MyCursor
				INTO @ColExpir,@ColWorkid
				END
				CLOSE @MyCursor
				DEALLOCATE @MyCursor
		
					
			SET @Flag	= @Flag		+ 1
			SET @PositionNr	= @PositionNr	+ 1
		 END
	 IF(@Flag > @counterCheck)
		BREAK
	 ELSE
		 CONTINUE
 END

Open in new window

ZURINETAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim P.Connect With a Mentor Commented:
This might work, but I'm not sure. I'm not sure if the cursor can be changed on the position number after it is created.

DECLARE @Flag INT,
		@counterCheck int,
		@PositionNr INT
		
 SET @Flag = 0
 SET @PositionNr = 1
 SET @counterCheck = (SELECT COUNT(*) FROM myTransaction WHERE TransactionID = 9010141)
 -- Get the number of Position in MyTransaction
 
 WHILE (@Flag < @counterCheck)
 BEGIN
		 BEGIN
			PRINT @Flag

					SET NOCOUNT ON;

				DECLARE @ColExpir datetime
				DECLARE @ColFallprotec datetime
				DECLARE @ColWorkid int
				--------------------------------------------------------
				DECLARE @MyCursor CURSOR
				SET @MyCursor = CURSOR FAST_FORWARD
				FOR
				SELECT DISTINCT Table_Training_Detalis.DateExpires,Table_Training_Detalis.Worker_ID
				FROM   Table_Courses 
				-- Use the PositionNr as a variable
				WHERE  PositionNr = @PositionNr
				
				
				OPEN @MyCursor
				FETCH NEXT FROM @MyCursor
				INTO @ColExpir,@ColWorkid
				WHILE @@FETCH_STATUS = 0
				BEGIN
					update Table_Workers set WHIMIS= @ColExpir where Worker_ID=@ColWorkid
					SELECT @PositionNr = @PositionNr + 1
					FETCH NEXT FROM @MyCursor
					INTO @ColExpir,@ColWorkid
				END

				
				CLOSE @MyCursor
				DEALLOCATE @MyCursor
		
					
			SET @Flag	= @Flag		+ 1
			SET @PositionNr	= @PositionNr	+ 1
		 END
	 IF(@Flag > @counterCheck)
		BREAK
	 ELSE
		 CONTINUE
 END

Open in new window

0
 
David ToddSenior DBACommented:
Hi,

Simple answer is

select count( * )
from dbo.TableCourses
;

HTH
  David
0
 
Anthony PerkinsCommented:
is this possible.. if yes..
I am sure it is possible.  But that is not a good idea at least not unless you like watching paint dry.  Why don't you tell us what you are trying to do in English?  Showing the before and after and I suspect we can probably change that to a single UPDATE statement.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
JimFiveCommented:
I'm not sure what PositionNr is supposed to be doing here, but I don't think you need a cursor.

Also, your cursor query references Table_Training_Detalis but is FROM Table_Courses which doesn't make sense.

It looks like you are wanting something like:
UPDATE Table_Workers
Set WHIMIS= Table_Training_Detalis.DateExpires
FROM Table_Training_Detalis
WHERE Table_Workers.Worker_ID = Table_Training_Detalis.Worker_ID 
--With some connection to the TransactionID here also

Open in new window

0
 
ZURINETAuthor Commented:
Hi all
Thanks for your input..
I just outsource the code..
This means .. I created a storeprocedure.. and call the storeprocedure from a while loop and
pass my variables.. and it works

Now Just one last question. Since I used BREAK
on the while loop..  Which is breaking all the code..
What I need to achive is this .. as soon as the while loop has reached the maximum value..
I need to exit the loop and move to the next query..

I.e

1: Query do some proccessing.. Control checks
2: If the check is okay.. Get values..
3: Insert values until I have reach the maximum (Since I have break here.. My code stops)
4: clean up isolated transaction and write a report.

How can I continue to step 4

Thanks in Advance..

Sorry I am not a native speaker  :-)
0
 
ValentinoVBI ConsultantCommented:
It's obvious that you've got a developer background.  When SQL Server pros see cursors and loops like this, they run away screaming or increase their pay rate by four...

You should learn to think set-based, it will make your life a lot easier, and your code easier to read and maintain plus it will run faster.  Wouldn't that be a nice achievement?

Here are a couple of articles that should help to get you started:
Programming SQL in a Set-Based Way
The Road to Professional Database Development: Set-Based Thinking

PS: this is not criticism, everyone has gone through this process, just think of it as the next step up!
0
 
ZberteocCommented:
@ZURINET

You should listen what people are saying here. You definetely DON"T need cursor for this task. I can show you 1 single update statement that can replace the whole cursor portion and will also run MUCH faster:

Replace this:
				DECLARE @ColExpir datetime
				DECLARE @ColFallprotec datetime
				DECLARE @ColWorkid int
				--------------------------------------------------------
				DECLARE @MyCursor CURSOR
				SET @MyCursor = CURSOR FAST_FORWARD
				FOR
				SELECT DISTINCT Table_Training_Detalis.DateExpires,Table_Training_Detalis.Worker_ID
				FROM   Table_Courses 
				-- Use the PositionNr as a variable
				WHERE  PositionNr = @PositionNr
				
				
				OPEN @MyCursor
				FETCH NEXT FROM @MyCursor
				INTO @ColExpir,@ColWorkid
				WHILE @@FETCH_STATUS = 0
				BEGIN
					update Table_Workers set WHIMIS= @ColExpir where Worker_ID=@ColWorkid
					SELECT @PositionNr = @PositionNr + 1
					FETCH NEXT FROM @MyCursor
					INTO @ColExpir,@ColWorkid
				END

				
				CLOSE @MyCursor
				DEALLOCATE @MyCursor

Open in new window


With this:
update workers set
	WHIMIS= Table_Training_Detalis.DateExpires
from
	Table_Workers as workers
	inner join Table_Courses as Table_Training_Detalis
		on Table_Training_Detalis.Worker_ID=workers.Worker_ID
where
	Table_Training_Detalis.PositionNr=@PositionNr

Open in new window


I didn't undertand this part from your code:
SELECT Table_Training_Detalis.DateExpires,Table_Training_Detalis.Worker_ID
				FROM   Table_Courses 
				-- Use the PositionNr as a variable
				WHERE  PositionNr = @PositionNr

Open in new window

Your SELECT is from Table_Courses table but you qualify your columns with Table_Training_Detalis. Where did that come from? I assumed that is an alias you gave to the Table_Courses table, which I used in my update example.
0
 
ZURINETAuthor Commented:
It works after twicking
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.