Is it possible to make a batch of “Alter procedure” that are stored in a table?
1
vote
0
answers
79
views
Currently I have hundreds of sp that are in multiple servers, and that coexist with other sp that are in other servers with linked server.
The origin of this is that when I do a restore of databases in development I need that these now point to their corresponding test version.
Then I already created a script that searches in all the sp and saves in a table the sp with the modifications that I require.
Now I can't manage to execute the sp to make the alterations of the SP.
-- Cursor variables
DECLARE @currentId INT;
DECLARE @currentSchema NVARCHAR(128);
DECLARE @currentName NVARCHAR(128);
DECLARE @currentScript NVARCHAR(MAX);
DECLARE @errorMessage NVARCHAR(MAX);
DECLARE @cleanScript NVARCHAR(MAX);
-- Start cursor
DECLARE procedure_cursor CURSOR FOR
SELECT Id, SchemaName, ProcedureName, AlterScript FROM AlterProcedures
ORDER BY Id;
OPEN procedure_cursor;
FETCH NEXT FROM procedure_cursor INTO @currentId, @currentSchema, @currentName, @currentScript;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXEC(@currentScript);
PRINT 'Updated ' + @currentSchema + '.' + @currentName + ' successfully.';
END TRY
BEGIN CATCH
SET @errorMessage = 'Error in update: ' + @currentSchema + '.' + @currentName + ': ' + ERROR_MESSAGE();
PRINT @errorMessage;
-- continue with the next even if there is wrong
END CATCH
FETCH NEXT FROM procedure_cursor INTO @currentId, @currentSchema, @currentName, @currentScript;
END
CLOSE procedure_cursor;
DEALLOCATE procedure_cursor;
--TRUNCATE TABLE AlterProcedures;
PRINT 'Process completed'
Result:
Error al actualizar dbo.SIIF_MotorMantenimientoFechaEjercida_sp: 'CREATE/ALTER PROCEDURE' debe ser la primera instrucción en un lote de consultas.
--> create/alter procedure' must be the first statement in a query batch
Asked by Angel Zapata Marquez
(19 rep)
May 26, 2025, 05:08 PM
Last activity: Jun 6, 2025, 01:11 PM
Last activity: Jun 6, 2025, 01:11 PM