question

helllo abit confused ... so all I should change in the stored procedure for it to rebuild more than one index is is just to change the set SET @sql = ( SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + @Task_Name FROM sys.indexes I INNER JOIN sys.tables T ON I.object_id = T.object_id INNER Join sys.schemas s ON s.schema_id = t.schema_id WHERE I.type_desc = 'NONCLUSTERED' AND I.name IS NOT NULL --AND I.is_disabled = 0 AND t.name = @Table_Name AND s.name = @Schema_Name ) --Execute the dynamic Sql EXEC (@sql) to be revised to set @sql =' ' ; SELECT @sql = @sql + 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + @Task_Name + ' '; FROM sys.indexes I INNER JOIN sys.tables T ON I.object_id = T.object_id INNER Join sys.schemas s ON s.schema_id = t.schema_id WHERE I.type_desc = 'NONCLUSTERED' AND I.name IS NOT NULL --AND I.is_disabled = 0 AND t.name = @Table_Name AND s.name = @Schema_Name AM I CORRECT

SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + @Task_Name FROM sys.indexes I INNER JOIN sys.tables T ON I.object_id = T.object_id INNER Join sys.schemas s ON s.schema_id = t.schema_id WHERE I.type_desc = 'NONCLUSTERED' AND I.name IS NOT NULL --AND I.is_disabled = 0 AND t.name = @Table_Name AND s.name = @Schema_Name ) - this query returns more than one values. how can i take them in loop?

The construction SELECT @sql = @sql + 'something' FROM table will cause the variable to be updated for every row in the underlying result set. So the SELECT @var=value is run row by row. That's how it's done even if you don't use the concatenation as I did, only then you loose all but the last row.