CREATE PROCEDURE DBO.ReorganizeWareGroupSortNumbers AS
-- Created AV 2006 0501

DECLARE @TheWareGroupNumber INT
DECLARE @Idx INT

IF EXISTS(SELECT WareGroups.SortNumber FROM WareGroups WITH (NOLOCK) 
WHERE LTRIM(RTRIM(MarkAsDeleted)) = '0'
GROUP BY WareGroups.SortNumber
HAVING COUNT(*) > 1)

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

  SELECT  WareGroupNumber FROM WareGroups WITH (NOLOCK)
  WHERE LTRIM(RTRIM(MarkAsDeleted)) = '0'
  ORDER BY SortNumber

  OPEN UpdateCur
  
  FETCH NEXT FROM UpdateCur INTO @TheWareGroupNumber
 
  While(@@Fetch_status=0)
  BEGIN
    UPDATE WareGroups SET SortNumber = @IDX WHERE WareGroupNumber = @TheWareGroupNumber
    SET @IDX = @IDX + 1
    FETCH NEXT FROM UpdateCur INTO @TheWareGroupNumber
  END

  CLOSE UpdateCur

  DEALLOCATE UpdateCur  

END

