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:

1. setting the parameter to its maximum value (i.e. 9223372036854775807 = 2^64 - 1 - or lesser, if the parameter has a smaller type like in the sample, or even worse: counting the rows befrorehand) and then simply using TOP nMaxCount,
2. splitting the select into two statements in an if statement - one with TOP nMaxCount for non-null values, one with either TOP ALL or without a TOP clause for the NULLed argument,
3. using dynamic SQL to add the TOP clause only if needed?

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"...