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.
Best Answer
If you don't need the result values later you can do it shorter this way:
The variable engine will produce the following query batch
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