How can I automatically update hundreds of T-SQL stored procedures?

Question

I want to add the word commit after the words @p_TEXT = @temp; in hundreds of T-SQL stored procedures

What I came up with is to run the following code, select all rows with the mouse, copy and then execute

SELECT CONCAT (
        REPLACE(REPLACE(TEXT, 'CREATE PROCEDURE', 'ALTER PROCEDURE'), '@p_TEXT = @temp;', '@p_TEXT = @temp;commit;')
        ,CHAR(10), 'GO')
FROM sys.procedures p
JOIN sys.syscomments s ON p.object_id = s.id
WHERE TEXT LIKE '%@p_TEXT%' 
  AND LEN(text) < 4000

I had to add a condition LEN(text) < 4000 because otherwise I would lose any text after 4000 characters. These are only a few, so I could do these manually.

The main problem is that if there are any comments in the stored procedure, then since the stored procedure is retrieved from the database as a single line, everything after the initial comment is treated as a comment.

ALTER PROCEDURE example @param1 INT ,@param2 -- comment   ,@param3 ,@P_Result INT OUTPUT  AS 

Any ideas of either improving this code or doing it differently?

Answer 1

since the stored procedure is retrieved from the database as a single line

This is caused by having "Retain CR/LF" switched off in SSMS.

Having said that, you can actually make all the modifications in one go in code, by using a cursor.

• Don't use sys.syscomments it's deprecated. Use sys.sql_modules instead.
• GO is not necessary (and doesn't work) when using EXEC. Use separate batches instead.
• Note that this is going to break if any of your procedures do not have the exact text CREATE PROCEDURE

DECLARE @sql nvarchar(max), @crsr CURSOR;

SET @crsr = CURSOR FOR
SELECT
  REPLACE(
    REPLACE(
      s.definition,
      'CREATE PROCEDURE',
      'ALTER PROCEDURE'
    ),
    '@p_TEXT = @temp;',
    '@p_TEXT = @temp;
commit;')
FROM sys.procedures p
JOIN sys.sql_modules s ON p.object_id = s.object_id
WHERE s.definition LIKE '%@p_TEXT%';

OPEN @crsr;

WHILE 1=1
BEGIN
    FETCH NEXT FROM @crsr INTO @sql;
    IF @@FETCH_STATUS <> 0
        BREAK;

    PRINT @sql;
    EXEC sp_executesql @sql;
END;

db<>fiddle