SQL Server For Each Procedure

I created my sp_ForEachProcedure base on Microsoft undocumented sp_msForEachTable.  It doesn’t have all the features of sp_msForEachTable, but does include a few of them.  I even added a few custom parameters (@print, @execute).   I did dynamic SQL because I was having problems creating the where statement.  I believe I could use a case, but went ahead and used the dynamic sql.  I would like to join to syscomments and search the actual stored procedure, but ran out of time. 
 
In the future I plan on creating sp_ForEachView
 
 
 

–example of sp_msforeachtable

–exec sp_msforeachtable @command1=’PRINT ”?”’, @whereand=’ and o.name like ”%Human%”’

 
ALTER

PROCEDURE sp_ForEachProcedure

  @command1

VARCHAR(MAX),

  @whereand

VARCHAR(MAX) = NULL,

  @print

BIT = 1,

  @execute

BIT = 0

AS

–DECLARE @command1 VARCHAR(MAX)
–DECLARE @whereand VARCHAR(MAX)
–SET @command1 = ‘PRINT ”?”’
–SET @whereand = ‘name like ”%contact%”’
 

DECLARE

@executeCommand NVARCHAR(MAX)

SET

@executeCommand =

  ‘ DECLARE @statementTable TABLE (ID INT IDENTITY(1,1) ,statement NVARCHAR(MAX)) ‘

+

  ‘ DECLARE @userCommand NVARCHAR(MAX) ‘

+

  ‘ INSERT INTO @statementTable (statement) ‘

+

  ‘ SELECT ‘

+

  ‘ REPLACE(”’

+ REPLACE(@command1, ””, ”””) + ”’, ”?”, ”[” + p.name + ”]”)’ + ‘ AS Statement’ +

  ‘ FROM SYS.Procedures p ‘

IF

(@whereand IS NOT NULL)

BEGIN

  SET @executeCommand = @executeCommand +

  ‘ WHERE ‘

+ REPLACE(@whereand, , ””)

END

IF

(@print = 1)

BEGIN

  SET @executeCommand = @executeCommand + ‘ SELECT * FROM @statementTable’

END
IF

@execute = 1

BEGIN

  SET @executeCommand = @executeCommand +

  ‘ DECLARE @loopCount INT ‘ +

  ‘ DECLARE @statement NVARCHAR(MAX) ‘ +

  ‘ SELECT @loopCount = COUNT(*)FROM @statementTable ‘ +

  ‘ WHILE @loopCount <> 0 ‘ +

  ‘ BEGIN ‘ +

  ‘ SET @statement = (SELECT statement FROM @statementTable WHERE ID = @loopCount) ‘ +

  –‘ PRINT ”EXECUTING: ” + @Statement ‘ +

  ‘ EXEC sp_executesql @Statement ‘ +

  ‘ SET @loopCount = @loopCount – 1 ‘ +

  ‘ END ‘

END

–FOR DEBUGGING
–PRINT @executeCommand

EXEC

sp_executesql @executeCommand

GO
 

–sp_ForEachProcedure ‘PRINT ”?”’, ‘NAME LIKE ”%Human%”’, 1
–sp_ForEachProcedure ‘GRANT EXECUTE ON ? TO Scrub ‘, ‘NAME LIKE ”%Human%”’, 1, 1
 
References:
Is a Temporary Table Really Necessary (How not to use cursors):

Leave a Reply

Your email address will not be published. Required fields are marked *