SQL Any 10 - Dynamically calling a stored procedure

[Former Member]

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 · Accepted solution]

Here is an example:

1) first create the procedure that is going to be called:

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

2) now call the "callme" procedure using execute immediate:

begin
    declare @bigv long varchar;

    set @bigv = repeat( 'x', 10000 );
    execute immediate 'call callme( @bigv )';
end;

You should see in your database server console the message:

callme has been called with length(v) = 10000

[Former Member · Accepted solution]

You nailed exactly what I was doing wrong. I had forgotten to put the CALL in the string before I tried to execute it. I knew it was something simple I was just not seeing.

Thanks for the assist! Working great now!