Midrange News for the IBM i Community


Posted by: renojim
Returning arrays from stored procedures
has no ratings.
Published: 08 Mar 2016
Revised: 12 Mar 2016 - 409 days ago
Last viewed on: 24 Apr 2017 (960 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Returning arrays from stored procedures Published by: renojim on 08 Mar 2016 view comments(3)

Reading this article about creating an array udt and using it in stored procedure:

SQL Arrays in DB2 for 7.0

Is there a way to call an SP and return an array using this?

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: Ringer
Premium member *
Comment on: Returning arrays from stored procedures
Posted: 1 years 1 months 15 days 16 hours 26 minutes ago

I didn't read the article but returning an array from a SP sounds odd to me when you can return a result set which is basically an array of table rows with meta data attached to it. 

Chris Ringer

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Returning arrays from stored procedures
Posted: 1 years 1 months 14 days 18 hours 11 minutes ago

A PROCEDURE returns an integer (or nothing), so technically, no. What they appear to be doing is using an "OUT" parameter as a way around this.

You must be thinking of executing this PROCEDURE from RPG, or something else not SQL. If you were using SQL you'd do a CREATE FUNCTION ... RETURNS TABLE.

Which begs the question, what are you trying to accomplish, that you need to return an array from a SQL PROCEDURE?

Posted by: renojim
Premium member *
Comment on: Returning arrays from stored procedures
Posted: 1 years 1 months 12 days 12 hours 33 minutes ago

Sorry for dawdling. I was asked if it's possible to return an array from an sql stored procedure to an rpg program. From what I read, the answer is no, but we can get the job done using result sets, and that's what I answered. haven't heard anything more on it, and been to busy to ask.

Thanks for the response!