question

dekolaguy avatar image
dekolaguy asked

help with revising a stored proc to accommodate more than one index rebuild

I have this stored procedure and I want to revise it to be to rebuild more than one index. USE DBS2007 GO /****** Object: StoredProcedure [Landing].[usp_IndexDisableRebuild] Script Date: 15/02/2018 09:31:13 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [Landing].[usp_IndexDisableRebuild] ( @Schema_Name VARCHAR(256) , @Table_Name VARCHAR(256), @Task_Name VARCHAR(256) ) AS --Set To get the dynamic Script for DECLARE @sql VARCHAR(max) 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)
indexing
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Change the SET @sql to this: 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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
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?
0 Likes 0 ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.