Question Re: SQL Any 10 - Dynamically calling a stored procedure in Technology Q&A

SQL Any 10 - Dynamically calling a stored procedure

Former Member — Fri, 03 Jun 2011 12:13:57 GMT

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?

Re: SQL Any 10 - Dynamically calling a stored procedure

MarkCulp — Fri, 03 Jun 2011 13:16:05 GMT

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

Re: SQL Any 10 - Dynamically calling a stored procedure

MarkCulp — Fri, 03 Jun 2011 13:34:37 GMT

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

Re: SQL Any 10 - Dynamically calling a stored procedure

Former Member — Fri, 03 Jun 2011 15:09:41 GMT

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!