By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,611 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,611 IT Pros & Developers. It's quick & easy.

Recursive stored proc call

P: n/a
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

Feb 2 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Feb 2, 9:20 am, oregon...@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
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

Feb 2 '07 #2

P: n/a
On Feb 2, 10:14 am, "jefftyzzer" <jefftyz...@sbcglobal.netwrote:
On Feb 2, 9:20 am, oregon...@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

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

Feb 2 '07 #3

P: n/a
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
Feb 3 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.