Devart Forums

Code: Select all

Error=CREATE PROCEDURE [dbo].[AlterIndexesOn](@TableName NVARCHAR(80), @Rebuild BOOLEAN)
AS
BEGIN
  DECLARE @EnableDisable nvarchar(10);
  DECLARE @sql NVARCHAR(80);
 
  Set @EnableDisable = case @Rebuild when 1 then 'REBUILD' else 'DISABLE' END
Error=Incorrect syntax near 'END'.

Code: Select all

/****** Object:  Rule [dbo].[rule_BOOLEAN]    Script Date: 5/8/2015 8:33:25 AM ******/
CREATE RULE [dbo].[rule_BOOLEAN] 
AS
(@AType IN (0, 1))

GO

/****** Object:  UserDefinedDataType [dbo].[BOOLEAN]    Script Date: 5/8/2015 8:33:25 AM ******/
CREATE TYPE [dbo].[BOOLEAN] FROM [bit] NULL
GO


/****** Object:  StoredProcedure [dbo].[AlterIndexesOn]    Script Date: 5/8/2015 8:33:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AlterIndexesOn](@TableName NVARCHAR(80), @Rebuild BOOLEAN)
AS
BEGIN
  DECLARE @EnableDisable nvarchar(10);
  DECLARE @sql NVARCHAR(80);
 
  Set @EnableDisable = case @Rebuild when 1 then 'REBUILD' else 'DISABLE' END; 
  DECLARE cur_IDX CURSOR FOR
      select 'ALTER INDEX ' + I.name + ' ON ' + T.name + ' ' + @EnableDisable 
      from sys.indexes I
      inner join sys.tables T on I.object_id = T.object_id
      where I.type_desc = 'NONCLUSTERED'
      and I.name is not NULL
      AND I.is_disabled = @Rebuild /* if True(1) returns disabled indexes, if False(0) returns enabled indexes*/
      AND T.name = @TableName
  
  OPEN cur_IDX;
  FETCH NEXT FROM cur_IDX INTO @sql;
  WHILE @@FETCH_STATUS = 0
     BEGIN
        EXECUTE sp_executesql  @sql;
        FETCH NEXT FROM cur_IDX INTO @sql;
     END;
  CLOSE cur_IDX;
  DEALLOCATE cur_IDX;
END

GO