Before execution of Fixup_PriceID
ItemID Description PriceLevel_ID PKEY
100 Red Chilli 1KG 1 32434
100 Red Chili 1KG 5 52434
100 Red Chili 1KG 6 62437
100 Red Chili 1KG 8 82435
100 Red Chili 1KG 9 93434
200 Green Chilli 2KG 1 32235
300 Yellow Chilli 3KG 1 24345
300 Yellow Chilli 3KG 2 53453
400 Purple Chilli 3KG 1 34534
After execution of Fixup_PriceID
ItemID Description PriceLevel_ID PKEY
100 Red Chilli 1KG 1 32434
100 Red Chili 1KG 2 52434
100 Red Chili 1KG 3 62437
100 Red Chili 1KG 4 82435
100 Red Chili 1KG 5 93434
200 Green Chilli 2KG 1 32235
300 Yellow Chilli 3KG 1 24345
300 Yellow Chilli 3KG 2 53453
400 Purple Chilli 3KG 1 34534
Tested: SQL Server Express 2012
1. Create a Stored Procedure named
Fixup_PriceID
.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Mike Meinz
-- Create date: 12 March 2013
-- Description: Fixup PriceLevel_ID to ensure ascending sequential integers
-- within each ItemID
-- =============================================
CREATE PROCEDURE [dbo].[Fixup_PriceID]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @TEMPTABLE TABLE(
PKey int,
PriceLevel_ID int);
DECLARE TEMPTABLE_Cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT PKey,PriceLevel_ID from @TEMPTABLE order by PKey;
DECLARE @tmpPKey int;
DECLARE @tmpPriceLevel_ID int;
DECLARE @currItemID int,@currPkey int,@currPriceLevel_ID int;
DECLARE @lastItemID int,@lastPkey int,@lastPriceLevel_ID int;
Declare Price_Table_Cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT ItemId,Pkey,PriceLevel_ID from PRICE_TABLE order by ItemID,Pkey;
DECLARE @FIRST_TIME BIT;
DECLARE @idx int;
DECLARE @boolSEQUENCE_ERROR BIT;
BEGIN TRANSACTION FIXUPPRICELEVELID;
SET @FIRST_TIME = 1;
OPEN Price_Table_Cursor;
-- Get first row
FETCH NEXT FROM Price_Table_Cursor INTO @currItemID,@currPkey,@currPriceLevel_ID;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @FIRST_TIME = 1
BEGIN
SET @FIRST_TIME=0
SET @LastItemID=@currItemID
SET @LastPKEY=@currPKEY
SET @LastPriceLevel_ID=@currPriceLevel_ID
SET @idx=0
SET @boolSEQUENCE_ERROR=0
END
IF @currItemID = @lastITemID
-- Same ItemID as last time, insert the values into TEMPTABLE for later processing
BEGIN
SET @idx = @idx + 1
INSERT INTO @TEMPTABLE (PKey,PriceLevel_ID) Values(@currPkey,@currPriceLevel_ID);
-- Check to see if PriceLevel_ID is expected value
IF @idx <> @currPriceLevel_ID SET @boolSEQUENCE_ERROR=1
END
ELSE
-- New ItemID was found so start processing @lastItemID's rows
BEGIN
IF @boolSEQUENCE_ERROR = 1
-- Found at least one for the @lastItemID that was out of sequence
BEGIN
-- Rows for @lastItemID were out of sequence
-- Check which rows are out of sequence and fix them
SET @idx=1
Open TEMPTABLE_Cursor;
FETCH NEXT FROM TEMPTABLE_Cursor INTO @tmpPKey,@tmpPriceLevel_ID;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Check if PriceLevel_ID is expected value
IF @idx <> @tmpPriceLevel_ID
-- If not expected value, Update PRICE_TABLE
BEGIN
UPDATE PRICE_TABLE SET PriceLevel_ID=@idx WHERE ItemID=@lastItemID AND PKEY=@tmpPKey;
END
SET @idx=@idx + 1 -- Compute next expected PriceLevel_ID
FETCH NEXT FROM TEMPTABLE_Cursor INTO @tmpPKey,@tmpPriceLevel_ID;
END
Close TEMPTABLE_Cursor;
END
DELETE FROM @TEMPTABLE;
--
-- Put @currItemID's information into TEMPTABLE
--
SET @boolSEQUENCE_ERROR=0
SET @LastItemID=@currItemID
SET @LastPKEY=@currPKEY
SET @LastPriceLevel_ID=@currPriceLevel_ID
SET @idx=1
INSERT INTO @TEMPTABLE (PKey,PriceLevel_ID) Values (@currPkey,@currPriceLevel_ID);
-- Check to see if PriceLevel_ID is expected value
IF @idx <> @currPriceLevel_ID SET @boolSEQUENCE_ERROR=1
END
FETCH NEXT FROM Price_Table_Cursor INTO @currItemID,@currPkey,@currPriceLevel_ID;
END -- WHILE loop
--
-- Process @lastItemID's rows
--
IF @boolSEQUENCE_ERROR = 1
-- Found at least one for the @lastItemID that was out of sequence
BEGIN
-- Rows for @lastItemID were out of sequence
-- Check which rows are out of sequence and fix them
SET @idx=1
Open TEMPTABLE_Cursor;
FETCH NEXT FROM TEMPTABLE_Cursor INTO @tmpPKey,@tmpPriceLevel_ID;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Check if PriceLevel_ID is expected value
IF @idx <> @tmpPriceLevel_ID
-- If not expected value, Update PRICE_TABLE
BEGIN
UPDATE PRICE_TABLE SET PriceLevel_ID=@idx WHERE ItemID=@lastItemID AND PKEY=@lastPkey;
END
SET @idx=@idx + 1 -- Compute next expected PriceLevel_ID
FETCH NEXT FROM TEMPTABLE_Cursor INTO @tmpPKey,@tmpPriceLevel_ID;
END
Close TEMPTABLE_Cursor;
END
DELETE FROM @TEMPTABLE;
Close Price_Table_Cursor;
Deallocate TEMPTABLE_Cursor;
Deallocate Price_Table_Cursor;
COMMIT TRANSACTION;
END
2. Execute the Stored Procedure
Exec Fixup_PriceID