Solved

Combining Cursor and While Loop

Posted on 2014-02-18
8
346 Views
Last Modified: 2014-03-12
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

0
Comment
Question by:ZURINET
8 Comments
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi,

Simple answer is

select count( * )
from dbo.TableCourses
;

HTH
  David
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 15

Expert Comment

by:JimFive
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ZURINET
Comment Utility
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
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
@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
 

Author Closing Comment

by:ZURINET
Comment Utility
It works after twicking
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now