469,955 Members | 2,576 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,955 developers. It's quick & easy.

Recursive stored proc call

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
3 2983
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
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
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.

Similar topics

3 posts views Thread by Robert Song | last post: by
45 posts views Thread by John | last post: by
3 posts views Thread by mandible | last post: by
reply views Thread by mirandacascade | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.