Re: SQL Stored Procedure with Result Set

On 26 Mar 2013 14:48, Anderson, Kurt wrote:

<<SNIP>> I'm trying to switch the stored procedure to return the
SmallInt value as a single result set. <<SNIP>>

DECLARE rsCursor CURSOR FOR SELECT rtnTracking FROM sysibm/sysdummy1;
OPEN rsCursor;
<<SNIP>>

rtnTracking is a local variable:
Declare rtnTracking SmallInt;

<<SNIP>> I hope there's a better way to set a local variable to a
result set w/o doing a "fake" select.
<<SNIP>> I'm at IBM i 7.1

A derived-table [Nested Table Expression (NTE)] can be defined as a /fullselect/ which can be defined by a VALUES-clause [with a column-list to name the columns]. The VALUES-clause can name an expression, and AFaIK a variable name should qualify as an expression in that context. Thus I believe the following SELECT statement could eliminate the explicit reference to the SYSDUMMY1 [irrespective of the actual implementation, which on older releases, might be via the equivalent one-row TABLE named QSQPTABL]:

DECLARE rsCursor CURSOR FOR
SELECT myNTE.rtnTracking
FROM table( values(rtnTracking) ) as myNTE ( rtnTracking )
;

That may or may not qualify as having eliminated a "fake" SELECT to enable setting of "a local variable to a result set". However I presume that the reference to SYSDUMMY1 is the concern\issue, because the CURSOR is still going to need to be a SELECT statement.?

FWiW: Or, to avoid [possible confusion for] multiple references to the same apparent variable name [i.e. per using rtnTracking as both variable name and as a column identifier], perhaps instead of the prior declared SELECT statement to define the CURSOR, use this one:

DECLARE rsCursor CURSOR FOR
SELECT myNTE.myTracking
FROM table( values(rtnTracking) ) as myNTE ( myTracking )
;

Note: The parentheses for the values-clause are apparently optional for a single-column row-expression, though I do not recall ever trying that. Thus the following should be the equivalent to the above:

DECLARE rsCursor CURSOR FOR
SELECT myNTE.myTracking
FROM table( values rtnTracking ) as myNTE ( myTracking )
;

FWiW: When the parentheses are included inside of the TABLE(), the appearance is that of a table-function named VALUES having one argument [the variable rtnTracking] being passed, although I presume the SQL would infer that the intention is for a values-clause for one-row of the one-column from the context of the specification. I can not test that, as I only have v5r3. Or it is possible [but IMO unlikely] that the NTE specification would need to be specified as only the parenthesis; i.e. omitting the word "table" in the above.?