Workaround for restriction: Another Execute Immediate statement cannot be executed inside an Execute Immediate statement

Question

I understand that SAP ASE has a restriction for nested execute commands.

Because of which the following code shows error:

DROP PROCEDURE sp_proc1

CREATE PROCEDURE sp_proc1
AS
BEGIN
    DECLARE @cmd VARCHAR(20)
    SET @cmd = 'SELECT GETDATE()'
    EXEC (@cmd) 
END

EXEC sp_proc1


DROP PROCEDURE sp_proc2

CREATE PROCEDURE sp_proc2
AS
BEGIN
    DECLARE @cmd VARCHAR(20)
    SET @cmd = 'sp_proc1'
    EXEC (@cmd) 
END

EXEC sp_proc2

Error:

Another Execute Immediate statement cannot be executed inside an Execute Immediate statement., Error 11039, Procedure sp_proc1, Line 6

Here is the actual problem I am trying to solve. Maybe someone can think of a better way to approach it. I have a list of stored procedure names that I have to execute dynamically, this logic is implemented in an initial stored procedure.

Each of these dynamically running stored procedures can contain calls to other stored procedures. These internal stored procedures in turn can also have dynamic statement execution commands. For example, one of these procedures receives the name of a table, queries the fields and data types of the table, elaborates a create statement and creates a copy of the table with the same structure and different name (dummy table).

Because of the restriction, I was forced to make the call of the first stored procedure by using stored procedures that wrap the call from a java class. However, I have problems with the evaluation version of SAP 16 that I use. According to SAP, the version that is available for download from the page contains errors to implement java classes that are corrected with patches (which are not available for free download).

So I am evaluating other options in the way I am implementing this solution.

If you have had a similar problem or any ideas on how to solve it differently I would appreciate your ideas.

Answer 1

From that link in your question re: the execute command, there are 2 different uses of the execute command:

execute <stored_proc_name>

-- and

execute ( <string> )

And it just so happens that the <stored_proc_name> can be implemented via a @variable, eg:

declare @procname  varchar(255),
        @arg1      varchar(30)

---------------

select  @procname = 'sp_help',
        @arg1     = 'sysobjects'

print "########################### %1! %2!", @procname, @arg1

exec @procname @arg1

---------------

select  @procname = 'sybsystemprocs..sp_help',
        @arg1     = 'sp_helpdb'

print "########################### %1! %2!", @procname, @arg1

exec @procname @arg1
go

This generates:

########################### sp_help sysobjects

 Name       Owner Object_type  Object_status Create_date         
 ---------- ----- ------------ ------------- ------------------- 
 sysobjects dbo   system table  -- none --   Jan 23 2016  9:52AM 

(1 row affected)
 Column_name  Type              Length Prec Scale Nulls Not_compressed

... snip ...

########################### sybsystemprocs..sp_help sp_helpdb

 Name      Owner Object_type      Object_status Create_date         
 --------- ----- ---------------- ------------- ------------------- 
 sp_helpdb dbo   stored procedure  -- none --   Jul 17 2020 12:11PM 

(1 row affected)
 Parameter_name Type    Length Prec Scale Param_order Mode 
 -------------- ------- ------ ---- ----- ----------- ---- 
 @dbname        varchar    255 NULL  NULL           1 in   
 @order         varchar     20 NULL  NULL           2 in   
(return status = 0)

I don't know the structure of your code but I'm wondering if in your top-level code, instead of running execute ( 'store_proc_name_as_a_string ...' ), you could use execute @store_proc_name ...?