Master of None

ARTICLE FROM ORIGINAL SITE – Young and Dumb!

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.

Tiny URL for this post:
 Copy