<TAG>Simple HTML& Javascripting Tricks & Tips

 

T-SQL: Cursor Example

Jack Donnell,
jack@JackDonnell.com

Database Cursors have long been a favorite of mine. It allows you to take a subset of data and output the information in various ways. I use them for such things as building the body of an automated email with a cursor set within a stored proceedure. You can return output to build a cleaner report output, etc.Example

Select 'Case Created: '+convert(Varchar(20), @DATE ) + ' Case Number: '+ convert(varchar(10), @CaseNum)

We will start with the basic frame work and build from that:

We will use a two tables named SomeTable and Other Table
(The tables are actually from MySQL - I was lazy.)

# --------------------------------------------------------
#
# Table structure for table 'othertable'
#

CREATE TABLE othertable (
   id bigint(20) NOT NULL auto_increment,
   Eventid bigint(20) DEFAULT '0' NOT NULL,
   Assignedto varchar(20) NOT NULL,
   Timestamp timestamp(14),
   PRIMARY KEY (id),
   UNIQUE id (id)
);


# --------------------------------------------------------
#
# Table structure for table 'sometable'
#

CREATE TABLE sometable (
   id bigint(15) NOT NULL auto_increment,
   Event longtext NOT NULL,
   EventDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   Timestamp timestamp(14),
   PRIMARY KEY (id)
);

The two table( If they were real) would be joined by 
OtherTable.Eventid to Primary Key SomeTable.id.


Okay the Cursor!

--- This is optional, the cursor can run as a script or
--- you can compile it as a stored proceedure
--- Let's set a range for the system to lookup events

CREATE PROCEDURE USER_DATA_CURSOR
@StartDate DateTime, @EndDate DATETIME
AS
DECLARE MY_CURSOR Cursor --- We need to name It!

--- We need to Tell the System what it is for
--- We can do a complex SQl Statement with 16 joins
--- or a simple one like this one.
FOR
Select ID, EventDate, Event
From Sometable
where EVENT in ('Reboot_System', 'Failed_Login','Failed_Service')
and
EventDate Between @StartDate and @EndDate


Open My_Cursor --- (remember to CLOSE IT LATER)
--- We need to make containers for the Cursor Info
DECLARE @VAR1Number DateTime, @VAR2DateTime DATETIME @VarLongText LongText

Fetch NEXT FROM MY_Cursor INTO @VAR1Number, @VAR2DateTime, @VarLongText
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)

--- Here is where we put the fun stuff!!!
To add a blank Line
Print " "
-- This will display the the first row an on and on
Select @VArNumber, @var2DateTime, @VarLongText

---You Can Use the values to build a new query or look up additional values

--- This displays the assignto field from OtherTable

SELECT o.assignto, S.Event
FROM OtherTable O, Sometable S
WHERE o.eventid = s.id
ORDER BY o.assignto

--- You can even Convert the out put, put in conditional logic, etc.
--- Once you are finished with the first record then you it will check for a new value.
--- You can also supply logic to move back in forth in the cursor, etc.

FETCH NEXT FROMMY_CURSOR INTO @VAR1Number, @VAR2DateTime ,@VarLongText
END
CLOSE MY_CURSORr
DEALLOCATE MY_CURSOR
GO

To run this stored procedure:
Exec USER_DATA_CURSOR '1/1/2002', '3/1/2002'
Well if the tables existed.


Return to JackDonnell.com