Cursor to Loop Through Recordset

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 2008 Forums

Transact-SQL (2008)

Cursor to Loop Through Recordset

Author

Topic

swhetsell
Starting Member

4 Posts

Posted - 2010-03-31 : 14:34:46

Hello everyone. I am fairly new to SQL (only a few months working with it). I haven't quite grasped all the concepts, and the cursor has me a bit confused.

Here's what I would like to accomplish:

Using a stored procedure, I create a temporary table and select a set of records based on a realtime snapshot of my database. I need (the cursor?) to cycle through all records selected in the temp table and perform a series of inserts based on each record. In Access I used Do Until .EOF, but as that is not available in T-SQL I (mis?)understand that the cursor is the way to go using @@Fetch commands. On average the temp table would return around 50-75 records with a max around 700. Any help would be greatly appreciated.

Here is the code I have so far:

========= BEGIN SAMPLE CODE =========

Create Table #UnitClientSnapshot (
UnitNumber varchar(8)
,UnitStatus varchar(8)
,AlphaStatus varchar(1)
,IsJoined varchar(1)
,SectionNumber varchar(4)
,TimerExpr datetime2
,CCNo varchar(16)
,CurrentCommCenter char(2)
,PrimaryCommCenter char(2)
,NewUnitNumber varchar(8)
,UnitRank varchar(8)
,UnitName varchar(83))

-- Find units on duty
INSERT INTO #UnitClientSnapshot (UnitNumber, UnitStatus,AlphaStatus,IsJoined,SectionNumber,TimerExpr,CCNo,CurrentCommCenter,PrimaryCommCenter,NewUnitNumber,UnitRank,UnitName)
Select dbo.unitclient.UnitNumber,dbo.UnitClient.UnitStatus,AlphaStatus,IsJoined,dbo.UnitClient.SectionNumber,TimerExpr,dbo.UnitClient.CCNo,CurrentCommCenter,PrimaryCommCenter,NewUnitID,Dbo.vwpersonnel.UnitRank,OutboundAs,Mileage
FROM (dbo.UnitClient Left Join dbo.vwPersonnel On dbo.UnitClient.UnitNumber = dbo.vwPersonnel.UnitID) Left Join dbo.UnitLog On dbo.UnitClient.RCN = dbo.UnitLog.RCN

-- Log unit number updates
FETCH NEXT FROM #UnitClientSnapshot
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
INSERT INTO dbo.UnitLog (UnitNumber, UnitRank, UnitName,UnitStatus,fldDateTime,Details,CCNo,PCO,CommCenter,Mileage)
VALUES(
)
INSERT INTO dbo.UnitLog (UnitNumber, UnitRank, UnitName,UnitStatus,fldDateTime,Details,CCNo,PCO,CommCenter,Mileage)
VALUES(
)
INSERT INTO dbo.UnitLog (UnitNumber, UnitRank, UnitName,UnitStatus,fldDateTime,Details,CCNo,PCO,CommCenter,Mileage)
VALUES(
)
END
FETCH NEXT
END

========== END SAMPLE CODE ==========

PCO S P Whetsell, A.A.S.

PackRat
Starting Member

26 Posts

Posted - 2010-03-31 : 15:10:13

doesn't look like you've declared a cursor, doesn't paralel ADO exactly. The online help topics are very helpful on this subject.

however, cursors are to used as a last resort when no other 'set based' method will work, i'll almost promise that whatever you're doing can be done with a set based solution, meaning that you process all the rows in each insert in a single statement. in very very (I might actually need another very here) rare circumstances a cursor will outperform a set - one of those apparently being the computation of running aggregates over multiple columns.


DECLARE UnitClientSnapshot CURSOR 
	LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
	Select dbo.unitclient.UnitNumber, dbo.UnitClient.UnitStatus, AlphaStatus, IsJoined, dbo.UnitClient.SectionNumber, TimerExpr, dbo.UnitClient.CCNo, CurrentCommCenter, PrimaryCommCenter, NewUnitID,Dbo.vwpersonnel.UnitRank, OutboundAs,Mileage
	FROM (dbo.UnitClient Left Join dbo.vwPersonnel On dbo.UnitClient.UnitNumber = dbo.vwPersonnel.UnitID) Left Join dbo.UnitLog On dbo.UnitClient.RCN = dbo.UnitLog.RCN;

OPEN UnitClientSnapshot;

FETCH NEXT FROM UnitClientSnapshot;
WHILE @@FETCH_STATUS = 0
BEGIN
	-- do work --
	FETCH NEXT FROM UnitClientSnapshot;
END;
CLOSE UnitClientSnapshot;
DEALLOCATE OPEN UnitClientSnapshot;

_____________________________
wrote this on my TRS-80 COCO4

<PakRat/>

swhetsell
Starting Member

4 Posts

Posted - 2010-04-02 : 12:10:41

Thanks for your reply PackRat. I will try your solution tonight when I get back to work. The code serves to create 3 log entries (which could be reduced to 2). It logs a unit number is about to change, logs the old unit number off duty, and logs the new unit number on duty with the same status he had before.

If you believe there is a better way of doing this, I'm welcome to suggestions.

PCO S P Whetsell, A.A.S.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources