Create an SP that references a non existant SP

[serge]

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

[Serge Rielau]

serge wrote:

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?

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

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[serge]

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


"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3a*************@individual.net...

serge wrote:

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?

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

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[serge]

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


"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3a*************@individual.net...

serge wrote:

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?

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

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

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/infoce...n/r0000948.htm
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[serge]

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!


"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3a*************@individual.net... 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/infoce...n/r0000948.htm
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[serge]

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

[Serge Rielau]

serge wrote:

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

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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

serge wrote:

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
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[serge]

> 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

[Serge Rielau]

serge wrote:

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

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
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab