sql - 为什么我在关键字 'with' 附近收到语法错误？

Question

--Use 'Database'BEGIN TRANdeclare @name1 varchar(150)declare @name2 varchar(150)declare CRS Cursor for     SELECT         OBJECT_NAME(ind.OBJECT_ID) AS DBtable,        ind.name AS IndexName       FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats         INNER JOIN sys.indexes ind          ON ind.object_id = indexstats.object_id         AND ind.index_id = indexstats.index_id     WHERE indexstats.avg_fragmentation_in_percent > 10 and ind.name is not null    ORDER BY indexstats.avg_fragmentation_in_percent DESC;open CRS    fetch Next from CRS into @name1,@name2    While (@@FETCH_STATUS=0)     begin       select @name1 as [Table name],@name2 as [Index name];          ALTER INDEX @name2 ON @name1 REBUILD PARTITION = ALL           WITH (fillfactor=85,  PAD_INDEX  = on, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = on, SORT_IN_TEMPDB = on )       fetch next from CRS into @name1,@name2;     end close CRS Deallocate CRS      ROLLBACK

错误信息

Incorrect syntax near the keyword 'with' (for rebuild the indices). If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon

Answer 1

您需要使用Dynamic Sql。试试这个。

BEGIN TRAN

DECLARE @name1 VARCHAR(150)
DECLARE @name2 VARCHAR(150)
DECLARE CRS CURSOR FOR
  SELECT Object_name(ind.OBJECT_ID) AS DBtable,
         ind.NAME                   AS IndexName
  FROM   sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, NULL) indexstats
         INNER JOIN sys.indexes ind
                 ON ind.object_id = indexstats.object_id
                    AND ind.index_id = indexstats.index_id
  WHERE  indexstats.avg_fragmentation_in_percent > 10
         AND ind.NAME IS NOT NULL
  ORDER  BY indexstats.avg_fragmentation_in_percent DESC;

OPEN CRS

FETCH Next FROM CRS INTO @name1, @name2

WHILE ( @@FETCH_STATUS = 0 )
  BEGIN

      DECLARE @sql NVARCHAR(max)

      SET @sql='ALTER INDEX ' + Quotename(@name2) + ' ON '
               + Quotename(@name1)
               + ' REBUILD PARTITION = all WITH (FILLFACTOR=85, PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = ON )'

      EXEC sp_executesql
        @sql

      FETCH next FROM CRS INTO @name1, @name2;
  END

CLOSE CRS