Can RUNSQL be used in a CL program to call an SQL stored procedure that has OUT parameters?

------------------------------
Amy Vozza
------------------------------

#SQL

Dear Amy

As far as I can remember, neither STRSQL nor RUNSQL has a capability to receive any returned parameters from the called stored procedure. Only Run SQL Script tool has this capability. 

Why would you have a need for a CL program to receive a returned parameter from a stored procedure?  Are you aware that SQL script for IBM i can contain both CL commands and SQL statements (you start every line of CL command with  "CL:" and end it with the usual semicolon)?  Perhaps, writing an SQL script may be a solution for you here.  You run the script text file (stored in IFS as a stream file) with either Run SQL Script tool or RUNSQLSTM command.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom
------------------------------

I guess it depends what you're wanting to do with the contents of the output variable.
Dynamic compound statement would allow the procedure to be called from RUNSQL
BEGIN
  DECLARE Out1 CHAR(1);
  CALL Proc1('ABC', Out1);
END;


------------------------------
Kent Milligan
------------------------------

Original Message:
Sent: Mon November 14, 2022 01:51 AM
From: Satid Singkorapoom
Subject: RUNSQL in a CL Program

Dear Amy

As far as I can remember, neither STRSQL nor RUNSQL has a capability to receive any returned parameters from the called stored procedure. Only Run SQL Script tool has this capability. 

Why would you have a need for a CL program to receive a returned parameter from a stored procedure?  Are you aware that SQL script for IBM i can contain both CL commands and SQL statements (you start every line of CL command with  "CL:" and end it with the usual semicolon)?  Perhaps, writing an SQL script may be a solution for you here.  You run the script text file (stored in IFS as a stream file) with either Run SQL Script tool or RUNSQLSTM command.

------------------------------
Right action is better than knowledge; but in order to do what is right, we must know what is right.
-- Charlemagne

Satid Singkorapoom
------------------------------

Hi,

this seems a bit late but in case anyone else needs a solution for this.

With the following 2 examples you can run sql from CL and use the output data directly.

https://wiki.midrange.com/index.php/SQL_Lookup_with_Returned_Value_(LOOKUPSQL)

https://www.mcpressonline.com/programming/sql/using-dynamic-sql-in-cl-part-2retrieving-a-data-value

Best regards,

Stefan

------------------------------
Stefan Rumersdorfer
------------------------------