Solved

Combining Cursor and While Loop

Posted on 2014-02-18
8
350 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
ID: 39869229
Hi,

Simple answer is

select count( * )
from dbo.TableCourses
;

HTH
  David
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39869245
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
ID: 39869326
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 15

Expert Comment

by:JimFive
ID: 39869328
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
 

Author Comment

by:ZURINET
ID: 39869508
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
ID: 39869570
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
ID: 39873764
@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
ID: 39923222
It works after twicking
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

777 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