Sql-server – Same code in multiple stored procedures

Don't be tempted to re-invent the wheel. This problem has been solved many times before you came across it.

Take a look at schema comparison tools, such as those available in SQL Server Data Tools (SSDT), and commercial options like Red-Gate SQL Compare.

Going forward you can also use DDL Triggers to log certain DDL events, helping you catch outlying changes that only happened in one place. If you want to do more reading, I have a few tips about this handy feature here, here and here.

If you don't need the result values later you can do it shorter this way:

-- procedures to test with
create proc proc1 as print '1' return 0
GO
create proc proc2 as print '2' return 1
GO
create proc proc3 as print '3' return 0
GO

if object_id('dbo.testproc') is null exec('create procedure dbo.testproc as return(0)')
GO
alter PROCEDURE dbo.testproc
AS 
  DECLARE @result INT
        , @sql nvarchar(max) = N''
  DECLARE @tbl TABLE([step] INT, [pname] nvarchar(513))

  INSERT INTO @tbl ([step], [pname]) 
  VALUES (1, N'proc1'), 
         (2, N'proc2'),
         (3, N'proc3')
  -- Potentially many more procedures here

  select @sql = @sql + 'exec @result = ' + QUOTENAME(pname) + ' if @result <> 0 return;'
  from @tbl order by step

  exec sp_executesql @sql, N'@result int output', @result output

  if @result <> 0
    begin
      print 'do your cleanup'
    end 
GO

exec testproc

The variable engine will produce the following query batch

exec @result = [proc1] if @result <> 0 return;
exec @result = [proc2] if @result <> 0 return;
exec @result = [proc3] if @result <> 0 return;

When the batch is executed it will stop executing when there is a @result not zero and keep that value in the output parameter.

More traditional looping

If you want to loop over the procedures. Since there are no parameters (or the parameters are all the same) you can simply call exec @result = @proc

if object_id('dbo.testproc') is null exec('create procedure dbo.testproc as return(0)')
GO
alter PROCEDURE dbo.testproc
AS 
  DECLARE @result INT
        , @proc sysname
  DECLARE @tbl TABLE([step] INT, [pname] nvarchar(513))

  INSERT INTO @tbl ([step], [pname]) 
  VALUES (1, N'proc1'), 
         (2, N'proc2'),
         (3, N'proc3')
  -- Potentially many more procedures here

  declare c cursor fast_forward local
  for select pname from @tbl order by step

  open c
  fetch next from c into @proc
  while @@FETCH_STATUS = 0
    begin 
      exec @result = @proc 
      if @result <> 0
          BREAK

      fetch next from c into @proc
    end
  close c
  deallocate c

  if @result <> 0
    begin
      print 'do your cleanup'
    end 


GO

exec testproc