Re: Stored procedure

As an aside to the OP - you can call ANY IBM i programs from something like Crystal Reports - every program can be considered a stored procedure.

Having said that, you don't WANT to do this with every possible program. Maybe not even at all. And you will not get back things like return codes or output parameters and that kind of thing.

But you can use the SQL CALL to call QCMDEXC with 2 parameters and have it done. Like WRKSYSSTS will get you a spooled file on the i, IIRC. That would be something like this in SQL -

call qcmdexc('WRKSYSSTS', 9)

call qcmdexc('WRKSYSSTS OUTPUT(*PRINT)', 24)

If I run those in STRSQL I get the WRKSYSSTS screen for the first one and a message that the call completed for the second - and a spooled file. In batch (as it'd be from Crystal), you get the spooled file.

HTH
Vern

On 6/9/2015 1:50 PM, CRPence wrote:

On 02-Jun-2015 11:32 -0600, Hoteltravelfundotcom wrote:

I want to use a stored procure which have a lot of feature
like temp table loop variables, Can I do this in IBM i?

Yes.

[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzcreatep.htm]
_CREATE PROCEDURE_

[http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzsqlcontstmts.htm]
_SQL control statements_
"Control statements are SQL statements that allow SQL to be used in a manner similar to writing a program in a structured programming language. SQL control statements provide the capability to control the logic flow, declare and set variables, and handle warnings and exceptions. Some SQL control statements include other nested SQL statements.
..."

[http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzcompoundstmt.htm]
_compound-statement_

I have not seen any examples how to create stored procedure on IBM i.
that can be called from a crystal report.

I have seen many examples and I and others have offered a number of examples on these fora; search the archives. Also, I have not seen any examples of a DB2 for IBM i SQL Stored Procedure that can not be called from Crystal Reports.