SQL Server 2000: Cursors

Cursors can be considers as named result sets which allow a user to move through each record one by one. SQL Server 2000 provide different types of cursors to support different type of scrolling options.  

 

When we use a Select statement in a stored procedure to get some result, we can get a collection of all the records which satisfy the given criteria. This result set can be returned back as a whole to the client application. We can’t move through each individual record one by one in this situation inside a stored procedure. Therefore, whenever we find some situation where we need to process each record individually we can use cursors.

 

In order to work with a cursor we need to perform some steps in the following order

 

  1. Declare  cursor
  2. Open cursor
  3. Fetch row from the cursor
  4. Process fetched row
  5. Close cursor
  6. Deallocate cursor

 

So, let’s take a look at these steps in a little detail

 

First of all we need to declare a cursor with the help of Declare statement and in order to specify the result set contained by this cursor we use Select statement. For example we can define a cursor named “MyCur” and we can use a table named Users having two columns UserName, and Password.

 

Declare MyCur Cursor

For

Select * From Users

 

 

 

Next, we need to open the cursor so that we’ll be able to use it

 

 

 

Open MyCur

 

 

Now, fetch first row from this cursor and loop through the cursor records until the specified criteria is found

 

Declare @UserName Varchar(50)

Declare @Password Varchar(50)

 

Fetch Next From Cursor Into @UserName, @Password

 

            While @@Fetch_Status = 0

             Begin

 

--Check if appropriate row was found then process it

--Othewise

--Fetch the next row as we did in the previous fetch              statement

 

End

 

 

   

 

 

When we have worked with the cursor, we’ll close it and deallocate it so that there will remain no reference to this cursor any more.

 

 

Close MyCur

Deallocate MyCur

 


 

 

Failed attempt: because when the trigger calls itself the cursor scope is across recursive calls,

 

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tbTimeSubSection_UP] ON [dbo].[tbTimeSubSection] FOR UPDATE
AS
-- 20091216 Lindsay
-- Cascades updates to a keyvalue to related keyvalues
-- Trigger rewritten to conform per:
-- http://confluence.xxxxxxxxxx.govt.nz/confluence/display/MSACCESS/Propagation+of+keyvalues
--
-- Uses a cursor to process the list of updates values in INSERTED
-- Note:
-- - that cascading updates are only made if necessary
-- - this trigger is self cascading, ie an update to a D record updates the corresponding S or DD & P values here
-- however it is expected that the corresponding PP record is picked up by this trigger running for the updated DD value
-- - This permits an unlimited number of Sub section group levels to be added be added by the user.

-- ** BIG NOTE: An update trigger is called EVEN IF NO ROWS ARE ACTUALLY UPDATED BY AN UPDATE STATEMENT **

Print '-----------------'
Print 'Trigger start'
Print '-----------------'

SET NOCOUNT ON

DECLARE @tiID INT,
@suID INT,
@tsValue Money,
@suNumber varchar(40),
@suRowType CHAR(2),
@reID INT,
@DisplayRegion VARCHAR(255),

@MeasureType varchar(1),

@AnySiblingHasAValue INT, -- anything other than null counts as a value, 0 is ok
@SiblingsSum Money,
@SumParentRowType CHAR(2), -- ids the summed row within the measure this keyvalue contributes to
@SumParentsuID INT,
@CurrentSum Money,

@PctDRowType CHAR(2), -- for percent measures ids the source D record type the updated keyvalue contributes to
@PctTRowType CHAR(2), -- for percent measures id's the source T record type the updated keyvalue contributes to
@PctPRowType CHAR(2), -- for percent measures id's the source P record type the updated keyvalue contributes to
@PctPRowsuID INT,
@PctDRowValue Money,
@PctTRowValue Money,
@PctPValueNew Money,
@CurrentPValue Money,

@SectionGroupChild INT,
@SectionGroupSiblingsHaveValues INT,
@SectionGroupSiblingsSum Money

DECLARE InsertedCursor CURSOR FOR SELECT tiID, suID FROM Inserted
OPEN InsertedCursor

Fetch NEXT From InsertedCursor Into @tiID, @suID

WHILE @@Fetch_Status = 0
BEGIN
PRINT 'Cursor start'
-- Get updated tsValue attributes so we can use later
SET @reID = (SELECT reID FROM tbSubSection WHERE suID = @suID)

SET @suNumber = (SELECT suNumber FROM tbSubSection WHERE suID = @suID)
SET @suRowType = (SELECT suRowType FROM tbSubSection WHERE suID = @suID)

-- P or S
SET @MeasureType = ISNULL (
(SELECT DISTINCT left(ss1.suRowType,1) FROM tbSubSection ss1
JOIN tbSubSection ss2 ON ss1.suNumber = ss2.suNumber
WHERE ss1.suRowType like 'P%'
AND ss2.suID = @suID)
,'S')

print '@tiID: ' + convert(varchar(100),@tiID)
print '@suID: ' + convert(varchar(100),@suID)

print '@reID: ' + convert(varchar(100),@reID)
print '@suNumber: ' + convert(varchar(100),@suNumber)
print '@suRowType: ' + convert(varchar(100),@suRowType)

print '@MeasureType: ' + convert(varchar(100),@MeasureType)

---------------
-- Does the value updated directly contributes to a SUM for the same measure?
-- Query & set vars ready for any SUM value the updated keyvalue contributes to
---------------

SET @SumParentRowType = CASE @suRowType
WHEN 'D' THEN CASE WHEN @MeasureType <> 'P' THEN 'S' ELSE 'DD' END
WHEN 'T' THEN 'TT'
ELSE NULL END

print '@SumParentRowType: ' + isnull(@SumParentRowType,'NULL')

SET @SumParentsuID = ( SELECT suID FROM tbSubsection ss
WHERE ss.suNumber = @suNumber
AND ss.suRowType = @SumParentRowType)

print '@SumParentsuID: ' + isnull(Convert(VARCHAR(100),@SumParentsuID),'NULL')

SET @AnySiblingHasAValue = (SELECT COUNT(tsValue) FROM tbTimeSubSection tss
JOIN tbSubSection ss ON tss.suID = ss.suID
WHERE tss.tiID = @tiID
AND ss.suRowType = @suRowType
AND ss.suNumber = @suNumber
AND tsValue IS NOT NULL)

print '@AnySiblingHasAValue: ' + convert(varchar(100),@AnySiblingHasAValue)

SET @SiblingsSum = (SELECT SUM(tsValue) FROM tbTimeSubSection tss
JOIN tbSubSection ss ON tss.suID = ss.suID
WHERE tss.tiID = @tiID
AND ss.suRowType = @suRowType
AND ss.suNumber = @suNumber
AND tsValue IS NOT NULL)

-- Set to sum NULL if ALL contributing values are NULL
SET @SiblingsSum = CASE WHEN @AnySiblingHasAValue <> 0 THEN @SiblingsSum ELSE NULL END

print '@SiblingsSum: ' + ISNULL(convert(varchar(100),@SiblingsSum),'NULL')

-- Use the above vars to update the SUM value if applicable and if not already = @SiblingsSums
-- Does D -> S, D -> DD, T -> TT for the measure

SET @CurrentSum = ( SELECT tsValue
FROM tbTimeSubsection tss
WHERE tss.tiID = @tiID
AND tss.suID = @SumParentsuID)

print '@CurrentSum: ' + ISNULL(convert(varchar(100),@CurrentSum),'NULL')

print 'Checking for sum update needed'

IF ((@CurrentSum IS NULL AND @SiblingsSum IS NOT NULL)
OR (@CurrentSum IS NOT NULL AND @SiblingsSum IS NULL)
OR (@CurrentSum <> @SiblingsSum)) AND @SumParentsuID IS NOT NULL
-- IF (@CurrentSum <> @SiblingsSum AND @SumParentsuID IS NOT NULL)
BEGIN
print 'Updating sum'
UPDATE tbTimeSubsection
SET tsValue = @SiblingsSum
FROM tbTimeSubsection tss
WHERE tss.tiID = @tiID
AND tss.suID = @SumParentsuID

-- RAISERROR('Sum updated', 16, 1)

END

----------------

-- Does the value updated directly contribute to a Percent for the same measure?
-- Query & set vars ready for any Pct value the updated keyvalue contributes to

SET @PctDRowType = CASE @suRowType
WHEN 'D' THEN 'D'
WHEN 'T' THEN 'D'
WHEN 'DD' THEN 'DD'
WHEN 'TT' THEN 'DD'
ELSE NULL END

print '@PctDRowType: ' + ISNULL(@PctDRowType,'NULL')

SET @PctTRowType = CASE @suRowType
WHEN 'D' THEN 'T'
WHEN 'T' THEN 'T'
WHEN 'DD' THEN 'TT'
WHEN 'TT' THEN 'TT'
ELSE NULL END

print '@PctTRowType: ' + ISNULL(@PctTRowType,'NULL')

SET @PctPRowType = CASE @suRowType
WHEN 'D' THEN CASE WHEN @MeasureType <> 'P' THEN NULL ELSE 'P' END
WHEN 'T' THEN 'P'
WHEN 'DD' THEN 'PP'
WHEN 'TT' THEN 'PP'
ELSE NULL END

print '@PctPRowType: ' + ISNULL(@PctPRowType,'NULL')

SET @PctPRowsuID = ( SELECT suID FROM tbSubsection ss
WHERE ss.suNumber = @suNumber
AND ss.suRowType = @PctPRowType
AND (ss.reID = @reID OR @PctPRowType = 'PP'))

print '@PctPRowsuID: ' + ISNULL(convert(varchar(100),@PctPRowsuID),'NULL')

SET @PctDRowValue = ( SELECT tsValue FROM tbTimeSubsection tss
JOIN tbSubSection ss ON ss.suID = tss.suID
WHERE tss.tiID = @tiID
AND ss.suRowType = @PctDRowType
AND ss.suNumber = @suNumber
AND ss.reID = @reID
AND tsValue IS NOT NULL)

print '@PctDRowValue: ' + ISNULL(convert(varchar(100),@PctDRowValue),'NULL')

SET @PctTRowValue = ( SELECT tsValue FROM tbTimeSubsection tss
JOIN tbSubSection ss ON ss.suID = tss.suID
WHERE tss.tiID = @tiID
AND ss.suRowType = @PctTRowType
AND ss.suNumber = @suNumber
AND ss.reID = @reID
AND tsValue IS NOT NULL)

print '@PctTRowValue: ' + ISNULL(convert(varchar(100),@PctTRowValue),'NULL')

SET @CurrentPValue = ( SELECT tsValue FROM tbTimeSubsection tss
WHERE tss.tiID = @tiID AND tss.suID = @PctPRowsuID)

print '@CurrentPValue: ' + ISNULL(convert(varchar(100),@CurrentPValue),'NULL')

SET @PctPValueNew = CASE WHEN ISNULL(@PctTRowValue,0) <> 0 AND @PctDRowValue IS NOT NULL THEN @PctDRowValue/@PctTRowValue ELSE NULL END

print '@PctPValueNew: ' + ISNULL(convert(varchar(100),@PctPValueNew),'NULL')

-- RAISERROR('PCT params set', 16, 1)

-- Use the above vars to update the Pct value if applicable and if not already = @PctPRowValue
-- Does D/T -> P for the region/measure, DD/TT -> PP for the measure

print 'CHECKING for PCT update needed'
IF ((@CurrentPValue IS NULL AND @PctPValueNew IS NOT NULL)
OR (@CurrentPValue IS NOT NULL AND @PctPValueNew IS NULL)
OR (@CurrentPValue <> @PctPValueNew)) AND @PctPRowsuID IS NOT NULL
-- IF (@CurrentPValue <> @PctPValueNew AND @PctPRowsuID IS NOT NULL)
BEGIN
print 'Updating PCT'
UPDATE tbTimeSubsection
SET tsValue = @PctPValueNew
FROM tbTimeSubsection tss
WHERE tss.tiID = @tiID
AND tss.suID = @PctPRowsuID
END

---------------
-- Is the measure the updated value belongs to a child in a Sub Section Group Relationship?
-- Query & set vars ready for any Sub Section Group the updated keyvalue contributes to
-- 0 = false

-- Remember that Wellington1, Wellington2 etc all contribute to a 'Wellington' parent sub section record

SET @DisplayRegion = ( SELECT reDisplayRegion FROM tbRegion re
JOIN tbSubSection SS ON ss.reID = re.reID
WHERE SS.suID = @suID)

SET @SectionGroupChild = (SELECT COUNT(*) FROM tbSubSectionGroup WHERE ChildsuNumber = @suNumber)


-- Measure contributes to a Section Group parent measure?
-- Does D -> D, T -> T for a (display) region from child to parent measure

-- To allow for when the child measure is a member of more than 1 group we need to do this all in the query
-- (i.e. can't use vars per the above updates)

-- Subsequently for each ssg parent suNumber we need to query:

-- SSGc (child = @suNumber)
-- --> SSGp (On SSG1.parent suNumber = SSG2.parent suNumber)
-- --> SS (on SSG2.child suNumber - SS.suNumber)
-- --> TSS (on TSS.suID = SS.suID)
-- WHERE tiID = @tiID & suRowType = @suRowType & SS.DisplayRegion = @DisplayRegion

-- For our NULLS rule re if all child values are NULL the parent is NULL we rely
-- on the SUM function applying this rule for us...
Print 'Checking for Sub Section Groups required'
IF (@SectionGroupChild > 0)
BEGIN
Print 'Updating sub section groups- START'

UPDATE tbTimeSubSection
SET tsValue = csv.ChildSumValue
FROM tbTimeSubSection tss
JOIN tbSubsection ss on ss.suID = tss.suID
JOIN tbRegion re ON re.reID = ss.reID

JOIN ( SELECT ss.suNumber, ss.suRowType, re.reDisplayRegion, tss.tiID,
SUM(tss.tsValue) AS ChildSumValue FROM tbSubSectionGroup ssgc
JOIN tbSubSectionGroup ssgp ON ssgc.suNumber = ssgp.suNumber
JOIN tbSubSection ss ON ss.suNumber = ssgp.ChildsuNumber
JOIN tbRegion re ON re.reID = ss.reID
JOIN tbTimeSubsection tss ON ss.suID = tss.suID
WHERE tss.tiID = @tiID
AND ss.suRowType = @suRowType
AND ss.suRowType IN ('D', 'T')
AND re.reDisplayRegion = @DisplayRegion
AND ssgc.ChildsuNumber = @suNumber
GROUP BY ss.suNumber,ss.suRowType, re.reDisplayRegion, tss.tiID
) csv
ON csv.suNumber = ss.suNumber
AND csv.suRowType = ss.suRowType
AND csv.reDisplayRegion = re.reDisplayRegion
AND tss.tiID = csv.tiID
WHERE tss.tsValue <> ChildSumValue

END
Print 'Updating sub section groups - END'

Fetch NEXT From InsertedCursor Into @tiID, @suID

Print 'Cursor - END'

END

Close InsertedCursor

Deallocate InsertedCursor

Print '-----------------'
Print 'Trigger END'
Print '-----------------'