Nested procedures in procedures-CodePudding

Is it possible to create a procedure in SQL Server, in which another procedure can be passed as a parameter for execution? Analogue of generic methods.

For example:

ALTER PROCEDURE [db].[myProc]
...
 @externalProc              PROCEDURE?    
...
 
BEGIN

  DECLARE 2 datetime variables: start/end
  SET start = GETDATE()
  EXEC @externalProc
  SET end = GETDATE()
//Other work with the received data

END

The bottom line is that I want to create 1 generic procedure that will do the same for many other procedures.

CodePudding user response：

You can pass the procedure name as the parameter for the wrapper stored procedure and execute it dynamically. But, I would suggest avoiding this kind of dynamic procedure execution, as it can lead to many issues. Some of them:

we don't know what is the logic of the parameter procedure content
It can lead to SQL injection
It can lead to security issues

I am giving sample code for test case of how this can be done. I have not considered SQL injection and other things. I am just putting to show that it is possible.

-- stored procedure to call
create procedure dbo.test
as
select 1

-- Wrapper stored procedure
create procedure dbo.uber_proc
@procname VARCHAR(128)
as
begin
DECLARE @sql_stmt NVARCHAR(256) = 'EXEC '   @procname
EXEC(@sql_stmt)
end

-- Executing wrapper stored procedure
EXEC dbo.uber_proc 'dbo.test'

update thanks to @Iptr, we can also create the wrapper stored procedure as given below:

CREATE procedure dbo.uber_proc
@procname VARCHAR(128)
as
begin
  EXEC @procname
end