Error:
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