Recursive stored proc call

[oregondba]

I have a database I have inherited (new job). I am trying to baseline
the code and have done a DB2Look to get the code out of an existing
db. The DB2Look produced a SP with the following form:

Create SP1

(various code)

call sp1()

(various code)
end

This code will not compile because of the procedure is calling itself,
but the procedure does not yet exist so it cannot resolve the code.
Since this is currently in a working database I assume that it got
compiled, somehow. I've not run across this before and am stumped.
Is there some trick to getting a recursive call to compile?

thanks

Jack

[jefftyzzer]

On Feb 2, 9:20 am, [e-mail] wrote:

I have a database I have inherited (new job). I am trying to baseline
the code and have done a DB2Look to get the code out of an existing
db. The DB2Look produced a SP with the following form:

Create SP1

(various code)

call sp1()

(various code)
end

This code will not compile because of the procedure is calling itself,
but the procedure does not yet exist so it cannot resolve the code.
Since this is currently in a working database I assume that it got
compiled, somehow. I've not run across this before and am stumped.
Is there some trick to getting a recursive call to compile?

thanks

Jack

Hi, Jack:

Recursive SQL procedures are possible, but there's one thing you need
to do a little differently--precisely because of what you've bumped up
against--which is to make the call in dynamic SQL. The procession is
like so:

CREATE SP1()
*various and sundry declarations*
DECLARE v_SP_Call VARCHAR(100) DEFAULT 'CALL SP1()';--
*various and sundry code*
EXECUTE IMMEDIATE v_SP_Call;--
*various and sundry code*
END;

As to how it got compiled before, I'm stumped (if not skeptical), but
the way described here will work.

HTH,

--Jeff

[jefftyzzer]

On Feb 2, 10:14 am, "jefftyzzer" <[e-mail]:

On Feb 2, 9:20 am, [e-mail] wrote:

I have a database I have inherited (new job). I am trying to baseline
the code and have done a DB2Look to get the code out of an existing
db. The DB2Look produced a SP with the following form:

Create SP1

(various code)

call sp1()

(various code)
end

This code will not compile because of the procedure is calling itself,
but the procedure does not yet exist so it cannot resolve the code.
Since this is currently in a working database I assume that it got
compiled, somehow. I've not run across this before and am stumped.
Is there some trick to getting a recursive call to compile?

thanks

Jack

Hi, Jack:

Recursive SQL procedures are possible, but there's one thing you need
to do a little differently--precisely because of what you've bumped up
against--which is to make the call in dynamic SQL. The procession is
like so:

CREATE SP1()
*various and sundry declarations*
DECLARE v_SP_Call VARCHAR(100) DEFAULT 'CALL SP1()';--
*various and sundry code*
EXECUTE IMMEDIATE v_SP_Call;--
*various and sundry code*
END;

As to how it got compiled before, I'm stumped (if not skeptical), but
the way described here will work.

HTH,

--Jeff- Hide quoted text -

- Show quoted text -

May I hasten add, for the benefit of hair-splitters everywhere, that
the "v_SP_Call" variable doesn't necessarily need to be 100. Please
size appropriately for the real name (and possibly fully qualified
path) of the SP.

:-)

--Jeff

[Phil Sherman]

I think I did this a long time ago by building a dummy procedure that
didn't have the recursive call as a "seed" to allow the real procedure
to compile.

Phil Sherman
or*******@yahoo.com wrote:

I have a database I have inherited (new job). I am trying to baseline
the code and have done a DB2Look to get the code out of an existing
db. The DB2Look produced a SP with the following form:

Create SP1

(various code)

call sp1()

(various code)
end

This code will not compile because of the procedure is calling itself,
but the procedure does not yet exist so it cannot resolve the code.
Since this is currently in a working database I assume that it got
compiled, somehow. I've not run across this before and am stumped.
Is there some trick to getting a recursive call to compile?

thanks

Jack