CODEWITHSUNDEEP
c#databasestored-proceduresdb2ibm-midrange
ehh
ehh

Reputation: 3480

AS400 Call a stored procedure from another stored procedure

I am trying to call a stored procedure from another stored procedure. I tried different syntax but with no success.

The first stored procedure returns a table. For the test, I just want to return from the second stored procedure what I got from the first one.

First stored procedure (SP01):

BEGIN
DECLARE C2 CURSOR WITH RETURN FOR

SELECT DISTINCT TBL.*
FROM LIB.TABLE1 TBL;

OPEN C2 ;
END

It works fine when calling it from c#.

Second stored procedure (SP02):

BEGIN
DECLARE C2 CURSOR WITH RETURN FOR

CALL SP01();

OPEN C2 ;
END

I am getting an error:

Vendor Code: -104
Message: [SQL0104] Token SP01 was not valid. Valid tokens: ;. Cause ....
A syntax error was detected at token SP01.

What is the correct syntax / approach for SP02?

EDITED:

In ms access, I was able to create a query QUERY2 based on another query QUERY1:

SELECT * FROM QUERY1;

or even joining it like a table

SELECT * FROM TABLE1 INNER JOIN QUERY1 ON (TABLE1.FIELD1 = QUERY1.FIELD1);

I need to move all my tables and queries from mdb to AS400 and write a C# application that use those queries.

I do not see so much examples on the net, maybe my approach is wrong. I have multiple queries to run and each one depends on another one. I thought calling one stored procedure from my C# application and this one will call to another one and so on. Is it a correct way to run a series of queries that depends one to each other?

Or is there a way to call from my c# application to all the queries independently and from the code to build the dependency between them, look like this approach is wrong?

Upvotes: 0

Views: 1613

Answers (1)

jweberhard
jweberhard

Reputation: 586

If you are using IBM i version 6.1 or earlier, you cannot access result sets returned by a stored procedure using a language SQL stored procedure. For version 7.1 or later, you can use the ASSOCIATE RESULT SET LOCATORS statement to retrieve the result sets. See the ASSOCIATE LOCATORS statement in the SQL Reference manual (http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzassocloc.htm?lang=en) for more information.

Once you have the locator, you use the ALLOCATE CURSOR statement (http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzalloccsr.htm?lang=en) to get a cursor from the RESULT SET LOCATOR.

Further examples can be found here: http://www.itjungle.com/fhg/fhg082510-printer02.html

Upvotes: 1

Related Questions