AW: Stored procedure

But you can use the SQL CALL to call QCMDEXC with 2 parameters and have it

done.
As an aside: The QCMDEXC stored procedure is overloaded and can be called
with either one (CL command) or two (CL command and command length)
parameters.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Vernon Hamberg
Gesendet: Tuesday, 09.6 2015 21:07
An: Midrange Systems Technical Discussion
Betreff: 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/rbafzcrea
tep.htm]

_CREATE PROCEDURE_

[http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/d
b2/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/d
b2/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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.