SQL Any 10 - Dynamically calling a stored procedure

[#1]

I'm trying to set up a stored procedure that will handle calling another stored procedure that is passed in as a long varchar variable. I've been experimenting with EXECUTE IMMEDIATE, but so far I'm not getting anywhere with it.

I feel like I'm missing something simple, but haven't stumbled across it yet. Is there a way to do this or do I need to change the call to use the second stored proc normally?

[MarkCulp]

If my first sample solution doesn't answer your question, here is a more elaborate example:

1) first create a few procedures that can be called:

create or replace procedure callme1( in @v long varchar )
begin
    message 'callme1 has been called with length(v) = ' || length(@v);
end;

create or replace procedure callme2( in @v long varchar )
begin
    message 'callme2 has been called with length(v) = ' || length(@v);
end;

create or replace procedure callme3( in @v long varchar )
begin
    message 'callme3 has been called with length(v) = ' || length(@v);
end;

2) Create a procedure that is given an integer that selects which procedure is to be called and the value that is to be passed:

create or replace procedure call_some_proc( in @i int, in @v long varchar )
begin
    declare @stmt long varchar;
    set @stmt = 'call "callme' || @i || '"( @v )';  -- compose call statement
    execute immediate @stmt;
exception when others then
    message 'Attempt to call proc #' || @i || ' failed: ' || errormsg();
end;

3) now try calling each of the procedures:

begin
    declare @bigv long varchar;
    set @bigv = repeat( 'x', 10000 );
    call call_some_proc( 1, @bigv );
    call call_some_proc( 2, @bigv );
    call call_some_proc( 3, @bigv );
    call call_some_proc( 4, @bigv );  -- this will generate an error
end;

You should see in your database server console these messages:

callme1 has been called with length(v) = 10000
callme2 has been called with length(v) = 10000
callme3 has been called with length(v) = 10000
Attempt to call proc #4 failed: Procedure 'callme4' not found