A Simple Example of CURSOR
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[ResetSlNoInPart] Script Date: 05/05/2009 10:59:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[ResetSlNoInPart]
(
@Filter_AC int -- For which AC to perform action
)
AS
BEGIN
DECLARE
@ID int, -- To identify record in which SLNOINPART is to be updated
@AC int, -- AC
@Part int, -- Part
@OldAC int, -- Old AC (for comparison in loop)
@OldPart int, -- Old Part (for comparison in loop)
@SerialNo int -- Serial No.
-- Create a new cursor that gets the ID, AC and Part for selected AC ordered by requirements
DECLARE SerialLoop CURSOR FOR
SELECT id, ac_no, part_no
FROM E_Draft_2
WHERE ac_no=@Filter_AC
ORDER BY ac_no ASC, part_no ASC, section_no ASC, house_no_en ASC, age DESC
-- Initialize the comparison variables
SET @OldAC=0
SET @OldPart=0
-- Open and start the loop
OPEN SerialLoop
FETCH NEXT FROM SerialLoop
INTO @ID, @AC, @Part
-- Repeat the loop till records exist
WHILE @@FETCH_STATUS=0
BEGIN
-- Compare and see whether Part or AC have changed
IF (@OldAC<>@AC) OR (@OldPart<>@Part)
BEGIN
-- If AC or Part changed, reset serial to 1 and update the comparison varialbes
SET @SerialNo=1
SET @OldAc=@AC
SET @OldPart=@Part
END
-- Update the current record with whatever serial no is current
UPDATE E_DRAFT_2
SET SLNoInPart=@SerialNo
WHERE ID=@ID
-- Debug print
PRINT 'AC='+Convert(varchar, @ac)+', part='+Convert(varchar, @part)+', sl='+Convert(varchar,@serialno)
-- Increment Serial no
SET @SerialNo=@SerialNo+1
-- Move to next record
FETCH NEXT FROM SerialLoop
INTO @ID, @AC, @Part
END
-- Cleanup
CLOSE SerialLoop
DEALLOCATE SerialLoop
END
Thanks,
Nitin Sharma
