

CREATE PROCEDURE DBO.ReorganizeWareSortNumbers @AWareGroupNumber int AS
-- Created AV 2006 0501

DECLARE @TheWareNumber INT
DECLARE @Idx INT

IF EXISTS(SELECT Ware.SortNumber,WareGroups.Text,WareGroups.WareGroupNumber FROM Ware WITH (NOLOCK)
INNER JOIN WareGroups WITH (NOLOCK) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber
WHERe Ware.MarkAsDeleted IS NULL AND Ware.WaregroupNumber = @AWareGroupNumber
GROUP BY Ware.SortNumber, WareGroups.text, WareGroups.WareGroupNumber
HAVING COUNT(*) > 1)

BEGIN
  SET @IDX = 1
  DECLARE UpdateCur CURSOR LOCAL FAST_FORWARD FOR

  SELECT  WareNumber FROM Ware WITH (NOLOCK)
  WHERE WareGroupNumber = @AWareGroupNumber
  AND MarkAsDeleted IS NULL
  ORDER BY SortNumber

  OPEN UpdateCur
  
  FETCH NEXT FROM UpdateCur INTO @TheWareNumber
 
  While(@@Fetch_status=0)
  BEGIN
    UPDATE Ware SET SortNumber = @IDX WHERE WareNumber = @TheWareNumber
    SET @IDX = @IDX + 1
    FETCH NEXT FROM UpdateCur INTO @TheWareNumber
  END

  CLOSE UpdateCur

  DEALLOCATE UpdateCur  

END

