Say, I have a stored procedure to return a result set and that procedure allows to specify the maximum number of returned rows via a parameter (say, named "nMaxCount") to use in a SELECT TOP clause. Now say that parameter should be optional to allow to return all rows.
Is there a simple way to specify "all" rows without either of the three:
Here's a simple demo querying over the systab:
create or replace procedure "dba".STP_Test(in nMaxCount unsigned integer default null) begin /* -- code I want to avoid: if nMaxCount is null then set nMaxCount = 4294967295; /*= 2 ^ 32 - 1*/ end if; */ select top nMaxCount table_id, creator, table_name from sys.systab order by 1; end; call stp_Test(100); -- succeeds call stp_Test(); -- fails with SQLCODE -674 ("Statement's size limit is invalid") when the above lines are commented out.
I would appreciate not to have to code a maximum value by hand, particularly as there seems not to be a "portable" way to do so (i.e. there's no MAXINT() or MAXUNSIGNEDINT() function or constant I'm aware of, and using the expression 0-1 seems to be not valid to set an unsigned value).
In contrast, for an optional START AT parameter it's easy to use 1 as DEFAULT.
Question: Is there a simple solution I have not taken into account?
If not, I would think to suggest to allow the TOP clause with a variable with NULL meaning "ALL"...
asked 22 Oct '15, 09:31