Why am I getting a syntax error near keyword 'with'?

Question

--Use 'Database'
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
       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

Error message

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

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. — Mehrdad Alemi
Please edit your question to add the additional information there, instead of burying it in comments. While you're editing, please change the title to something that describes the problem you're having or the question you're asking. Please help me to find out my issue is meaningless, and will have no value to future users of this site who see it in a search result. Your title should actually explain something about the question, not just be a plea for help - we know you need help, or you wouldn't be asking here in the first place. — Ken White
What Ken White says. Moreover, why are you using loop inside the cursor to iterate through the indexes?! The first impression of this code is that it's insane, although I don't know what you're trying to achieve. Can you please describe what you're trying to achieve with this as well? — Widunder
I want to identify all the indices of the tables with fragmentation over 10% (Select statement) and rebuild them inside my database. I want to add this script as a job into my SQL Instance. — Mehrdad Alemi

Pரதீப் Pரதீப் · Accepted Answer · 2015-01-09T01:58:31

You need to use Dynamic Sql. Try this.

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

Why am I getting a syntax error near keyword 'with'?

1 Answers