Create an SP that references a non existant SP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

serge - 23 Mar 2005 02:44 GMT

I can not create a stored procedure that calls another not yet created
stored procedure?

In MS SQL I get a warning that the calling procedure does not exist but the
new stored

Procedure gets created anyhow. I believe Oracle works similarly.

I can not make this possible in DB2?

Thank you

Reply to this Message

Serge Rielau - 23 Mar 2005 12:52 GMT

> I can not create a stored procedure that calls another not yet created
> stored procedure?
[quoted text clipped - 5 lines]
>
> I can not make this possible in DB2?

Sure you can, just use a dynamic statement:
EXECUTE IMMEDIATE CALL .... USING

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

serge - 24 Mar 2005 03:02 GMT

If i take this example:

--DROP PROCEDURE proc1;
CREATE PROCEDURE proc1()
LANGUAGE SQL
BEGIN

DECLARE varInput INTEGER;
DECLARE varOutput INTEGER;

SET varInput = 2;

CALL proc2(varInput, varOutput);

END
;

--DROP PROCEDURE proc2;
CREATE PROCEDURE proc2(IN myInputVariable INTEGER,
OUT myOutputVariable INTEGER)
LANGUAGE SQL
BEGIN

SET myOutputVariable = myInputVariable + 5;

END
;

When i run this I get error:

21:01:22.836 DBMS MODEL0 -- Error: [IBM][CLI Driver][DB2/NT] SQL0440N
No authorized routine named "PROC2" of type "PROCEDURE" having compatible
arguments was found. LINE NUMBER=11. SQLSTATE=42884

Can you please tell me how you write the statement below?

EXECUTE IMMEDIATE CALL .... USING

An important question i have: If the procedure statement has Input and

Output parameters

CALL proc2(varInput, varOutput);

will the values of the parameters exist in the scope of the calling
procedure,

in this case proc1?

Thank you

>> I can not create a stored procedure that calls another not yet created
>> stored procedure?
[quoted text clipped - 10 lines]
> Cheers
> Serge

Reply to this Message

serge - 24 Mar 2005 03:03 GMT

>> I can not create a stored procedure that calls another not yet created
>> stored procedure?
[quoted text clipped - 10 lines]
> Cheers
> Serge

Reply to this Message

Serge Rielau - 24 Mar 2005 04:38 GMT

OK, I took a closer peek. Here is how you need to do it:

--DROP PROCEDURE proc1;
CREATE PROCEDURE proc1()
LANGUAGE SQL
BEGIN

DECLARE varInput INTEGER;
DECLARE varOutput INTEGER;
DECLARE txt VARCHAR(100);
DECLARE s STATEMENT;

SET varInput = 2;
SET txt = 'CALL proc2(?, ?)';
PREPARE s FROM txt;
EXECUTE INTO varOutput USING varInput;

END
;

See:
http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin
/r0000948.htm

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

serge - 24 Mar 2005 04:52 GMT

I think you made a little mistake.

> EXECUTE INTO varOutput USING varInput;

You forgot to add the "s", i was wondering what that "s" was being used for?

It should be:

EXECUTE s INTO varOutput USING varInput;

I ran this and got no error, i also quickly read the online help link you
gave me. This looks very interesting.

I'll continue working on this to learn exactly what the online help talks
about.

Thank you very much Serge!

> OK, I took a closer peek. Here is how you need to do it:
>
[quoted text clipped - 18 lines]
> See:
> http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin
/r0000948.htm

Reply to this Message

Serge Rielau - 24 Mar 2005 12:59 GMT

> I think you made a little mistake.
>
>> EXECUTE INTO varOutput USING varInput;
>
> You forgot to add the "s", i was wondering what that "s" was being used for?

s is a handle for the compiled statement.
I post way too much to be able to test every example. So what you get
from me are "fill in the blanks" type of hints. The price of free
Level-0 support from the Lab. One gets what on pays for *chuckle*

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

serge - 24 Mar 2005 06:18 GMT

However when i run in the Command Line Processor:

CALL PROC1()

I get

Return Status = 0

I can't understand why it's not working. I expect the result to be 7,
rather than 0.

I'll continue tomorrow to try to understand what might be wrong.

Thank you

Reply to this Message

Serge Rielau - 24 Mar 2005 12:56 GMT

> However when i run in the Command Line Processor:
>
[quoted text clipped - 10 lines]
>
> Thank you

TEh result of proc1? What you get is a return status, usually used as
teh error-code.
If you want a result back you need to add OUT parameters to the
procedure and set them.
To change the return status use the RETURN statement.
You shoudl not this statement to return data though. That would be an
abuse of the feature. Various client interfaces would not be happy.

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

serge - 24 Mar 2005 13:44 GMT

> You shoudl not this statement to return data though. That would be an
> abuse of the feature. Various client interfaces would not be happy.

Then should I conclude that it's not a good idea to use this method if
i have output parameters?

The only reason i wanted to do this is to be able to compile my stored
procedures. Since proc2 gets compiled after proc1, i should simply
make sure i always compile procedures that get called like proc2,
before compiling proc1?

Thank you

Reply to this Message

Serge Rielau - 24 Mar 2005 14:28 GMT

>>You shoudl not this statement to return data though. That would be an
>>abuse of the feature. Various client interfaces would not be happy.
[quoted text clipped - 8 lines]
>
> Thank you

These are two different issues.
Feel free to use dynamic calls and dynamic SQL in general.

Static or dynamic don't use the RETURN statement do get
the results for procedures other than status. That's what OUT parameters
are for.
DB2 Dev. added the RETURN statement explicitly to support client
interfaces looking for this status indicator.
It is not even part of the SQL/PSM standard.

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message