6

I have a stored procedure written in T-SQL (SQL Server 2008 R2). It is a master procedure that essentially call multiple other subprocedures in sequence. The call and error handling is identical for each one except for the name of the procedure.

In an OO language I would use an abstraction such as an interface or functor and loop over a bunch of objects. That does not work in SQL, but I want to find some way to make this code more concise with less copy and paste repetition. Yes, I know that fundamentally SQL is about set operations and does not support what I want to do very well, but if there is a way, it will make the code much more concise. I also need to capture the result of each stored procedure invocation and do something with it which is not relevant to this question.

Here is what I have so far:

CREATE PROCEDURE dbo.testproc
AS BEGIN
  DECLARE @step INT, @result INT
  DECLARE @tbl TABLE([step] INT, [pname] NVARCHAR(40))

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

  SET @step = 1
  WHILE @step <= (SELECT MAX([step]) FROM @tbl)
  BEGIN
    DECLARE @sql NVARCHAR(60)
    SET @sql = N'EXEC @result = dbo.' + (SELECT [pname] FROM @tbl WHERE [step] = @step)
    EXEC (@sql)
    IF @result <> 0
    BEGIN
      INSERT INTO SomeTable error code and step number
      RETURN
    END
   SET @step = @step + 1
  END
END
GO

When I run the procedure, SQL Server gives me an error because the @result variable that is part of the dynamic SQL is not defined as part of the batch that is contained in the @sql variable. If I modify it like this:

    SET @sql = N'EXEC dbo.' + (SELECT [pname] FROM @tbl WHERE [step] = @step)
    EXEC @result = (@sql)

I get a syntax error.

This works fine except for retrieving the return value of the subprocedures. Is there a way to accomplish my stated goal, and if so, how?

NOTE: based on what I asked here, a cursor would sound like a better implementation than a WHILE loop especially given the table variable. Part of the code that is not essential to this question involves knowing the iteration number, hence the use of a loop control variable.

migrated from stackoverflow.com Oct 14 '14 at 19:55

This question came from our site for professional and enthusiast programmers.

  • Back to @AaronBertrand's original comment, @result is used to control whether to continue to the next procedure or to perform cleanup and exit logic. It contains either 0 meaning success or non-zero containing an error code. Exceptions won't work here due to some custom error-handling logic and the fact that SQL Server will not let us throw standard error codes. – user48112 Oct 14 '14 at 20:00
  • It can be a temp table, does not matter really. My understanding is that table variables are easier to clean up, i.e. they do not require an explicit DROP TABLE at the end. – user48112 Oct 14 '14 at 20:01
  • Nor do #temp tables. – Aaron Bertrand Oct 14 '14 at 20:01
  • So you have custom error-handling logic that relies on the original error being thrown? And you need to use the error number that comes back separately? – Aaron Bertrand Oct 14 '14 at 20:26
  • The error handling occurs inside the subprocedure and it returns a value to the main procedure, which has to return a code via output parameter (I omitted that part for brevity). There are some funky requirements to fulfill here. Anyway, the accepted answer works beautifully. – user48112 Oct 14 '14 at 20:34
5

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
  • this procedure Returns a value , if it was an OUTPUT parameter this approach would work like a charm but not with a return parameter. – M.Ali Oct 14 '14 at 20:21
  • print the @sql statement and check what it does. The output param is only used to pick up the return value for the cleanup step later on. – Filip De Vos Oct 14 '14 at 20:23
  • And this is what I think is not possible, you cannot pick up stored procedure's return value with an OUTPUT parameter – M.Ali Oct 14 '14 at 20:26
  • I don't. The dynamic sql contains exec @result = proc1. The output param serves to lift the @result value outside the dynamic sql scope. – Filip De Vos Oct 14 '14 at 20:27
  • This works, thanks! I never knew this was possible (I am a developer, not a DBA). No more dynamic SQL (I prefer to avoid it whenever possible), and the total code is a LOT shorter. – user48112 Oct 14 '14 at 20:32
5

Another approach, which still uses dynamic SQL but no ugly cursor scaffolding (and allows you to examine the step which failed and the error number generated, without bubbling the error to the caller):

  DECLARE @step INT = 0, @result INT = 0, @sql NVARCHAR(MAX) = N'';
  DECLARE @tbl TABLE([step] INT PRIMARY KEY, [pname] NVARCHAR(513));    
  INSERT @tbl([step],[pname]) VALUES(1,N'dbo.proc1'),(2,N'dbo.proc2'),(3,N'dbo.proc3');

  SELECT @sql += N'
    SET @step = ' + CONVERT(VARCHAR(12), 
      ROW_NUMBER() OVER (ORDER BY step)) + ';
    IF @result = 0
    BEGIN
     BEGIN TRY
      EXEC @result = ' + pname + ';
     END TRY
     BEGIN CATCH
      SET @result = ERROR_NUMBER();
      RETURN;
     END CATCH' 
  FROM @tbl ORDER BY [step] OPTION (MAXDOP 1); 

  SET @sql += REPLICATE(N' END ', @@ROWCOUNT);

  EXEC sp_executesql @sql, N'@step INT OUTPUT, @result INT OUTPUT', 
    @step = @step OUTPUT, @result = @result OUTPUT;

  PRINT 'Failed at step ' + CONVERT(VARCHAR(12), @step);
  PRINT 'Error number was ' + CONVERT(VARCHAR(12), @result);

Not sure if you wanted the "Failed at step" value to be the step number (like your loop variable) or the actual value of step in the table. You can switch by changing this:

    SET @step = ' + CONVERT(VARCHAR(12), 
      ROW_NUMBER() OVER (ORDER BY step)) + ';

To this:

    SET @step = ' + CONVERT(VARCHAR(12), step) + ';
  • good catch on the procname length, I forgot about fully qualified names. – Filip De Vos Oct 14 '14 at 21:12
  • Interesting idea. It almost feels like a CTE but for dynamic SQL. This is overkill for my current requirement but I will definitely try to remember this for later. – user48112 Oct 14 '14 at 21:13
-1

Try using exec sp_executesql @sql,'@result int output', @result=@result output. This way you can pass variables in and out to your dynamic query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy