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?
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?
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
- → How to Fire Resize event after all images resize
- → URL routing requires /Home/Page?page=1 instead of /Home/Page/1
- → Getting right encoding from HTTPContext
- → How to create a site map using DNN and C#
- → I want integrate shopify into my mvc 4 c# application
- → Bootstrap Nav Collapse via Data Attributes Not Working
- → Shopify api updating variants returned error
- → ASP.NET C# SEO for each product on detail page on my ECOMMERCE site
- → SEO Meta Tags From Behind Code - C#
- → How to implement search with two terms for a collection?