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.