SET NOCOUNT ON 

DECLARE @IdxName VARCHAR(255)
DECLARE @sql NVARCHAR(2000)

DECLARE #TempCur CURSOR SCROLL STATIC FOR
  SELECT sysind.name AS INDEX_NAME
  FROM sys.indexes sysind 
  INNER JOIN sys.index_columns sysind_col ON sysind.object_id = sysind_col.object_id 
    AND sysind.index_id = sysind_col.index_id 
  INNER JOIN sys.columns sys_col ON sysind_col.object_id = sys_col.object_id 
    AND sysind_col.column_id = sys_col.column_id 
  INNER JOIN sys.tables systab ON sysind.object_id = systab.object_id
  WHERE systab.is_ms_shipped = 0 
  AND sysind.is_primary_key=0 
  AND sys_col.name  = 'WareLinkNumber'
  AND systab.name = 'Ware'
OPEN #TempCur

FETCH NEXT FROM #TempCur INTO @IdxName

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'DROP INDEX ' + @IdxName + ' ON Ware'
  EXEC sp_executesql @sql
  FETCH NEXT FROM #TempCur INTO @IdxName
END

SET @sql = N'ALTER TABLE Ware ALTER COLUMN WareLinkNumber INT NULL'
EXEC sp_executesql @sql

FETCH FIRST FROM #TempCur INTO @IdxName
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'CREATE NONCLUSTERED INDEX [' + @IdxName +'] ON [dbo].[Ware] ([WareLinkNumber] ASC)'
  EXEC sp_executesql @sql
  FETCH NEXT FROM #TempCur INTO @IdxName
END
CLOSE #TempCur
DEALLOCATE #TempCur

IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'WareLinkGroup')
CREATE TABLE [dbo].[WareLinkGroup](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[SortNumber] [int] NOT NULL,
--	[FK_RecurringPatternId] [INT] NULL,
 CONSTRAINT [PK_WareLinkGroup] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
) ON [PRIMARY]
GO

DECLARE @cnt INT

SELECT @cnt = COUNT(*) FROM WareLinkGroup WITH (NOLOCK)
IF ISNULL(@Cnt, 0) = 0
BEGIN
  INSERT INTO WareLinkGroup (Name, SortNumber) 
  SELECT KeyValue, CAST(SUBSTRING(SearchKey, 13, 2) AS INT)
  FROM HotelSetup WITH (NOLOCK) 
  WHERE SearchKey LIKE 'HallWareText%'
  AND ISNULL(KeyValue, '') <> ''
  ORDER BY CAST(SUBSTRING(SearchKey, 13, 2) AS INT)

  UPDATE Ware SET WareLinkNumber = (SELECT Id FROM WareLinkGroup WHERE SortNumber = 1)
  WHERE WareLinkNumber = 1
  UPDATE Ware SET WareLinkNumber = (SELECT Id FROM WareLinkGroup WHERE SortNumber = 2)
  WHERE WareLinkNumber = 2
  UPDATE Ware SET WareLinkNumber = (SELECT Id FROM WareLinkGroup WHERE SortNumber = 3)
  WHERE WareLinkNumber = 3
  UPDATE Ware SET WareLinkNumber = (SELECT Id FROM WareLinkGroup WHERE SortNumber = 4)
  WHERE WareLinkNumber = 4
  UPDATE Ware SET WareLinkNumber = (SELECT Id FROM WareLinkGroup WHERE SortNumber = 5)
  WHERE WareLinkNumber = 5
  UPDATE Ware SET WareLinkNumber = (SELECT Id FROM WareLinkGroup WHERE SortNumber = 6)
  WHERE WareLinkNumber = 6
  UPDATE Ware SET WareLinkNumber = (SELECT Id FROM WareLinkGroup WHERE SortNumber = 7)
  WHERE WareLinkNumber = 7
  UPDATE Ware SET WareLinkNumber = (SELECT Id FROM WareLinkGroup WHERE SortNumber = 8)
  WHERE WareLinkNumber = 8
  UPDATE Ware SET WareLinkNumber = (SELECT Id FROM WareLinkGroup WHERE SortNumber = 9)
  WHERE WareLinkNumber = 9
  UPDATE Ware SET WareLinkNumber = (SELECT Id FROM WareLinkGroup WHERE SortNumber = 10)
  WHERE WareLinkNumber = 10

  EXEC DoDeleteHotelSetupSetting 'HallWareText1'
  EXEC DoDeleteHotelSetupSetting 'HallWareText2'
  EXEC DoDeleteHotelSetupSetting 'HallWareText3'
  EXEC DoDeleteHotelSetupSetting 'HallWareText4'
  EXEC DoDeleteHotelSetupSetting 'HallWareText5'
  EXEC DoDeleteHotelSetupSetting 'HallWareText6'
  EXEC DoDeleteHotelSetupSetting 'HallWareText7'
  EXEC DoDeleteHotelSetupSetting 'HallWareText8'
  EXEC DoDeleteHotelSetupSetting 'HallWareText9'
  EXEC DoDeleteHotelSetupSetting 'HallWareText10'
END