How can I use the TOP clause with a default "show all" value?

[VolkerBarth]

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

[Former Member]

• Option 4: Build the SQL string conditionally on the nullability of your optional parameter and use Execute Immediate maybe?

[Former Member]

?:-/ not certain why that last post is shouting at you .... ?:-/

[VolkerBarth]

Well, that's what I had meant with option 3 ("dynamic SQL") - note that I want to avoid options 2 and 3 because the "real" queries are way more complex than in the sample and both "duplicating" the queries and building them dynamically are undesired.

Of course, if I would construct the query dynamically by design (say, from an application), then that would be the apparent way to go, but here I'm within a procedure context.

[VolkerBarth]

Well, the Markdown syntax doesn't allow to use a numbered list with a starting number other than 1, and "#4" is treated as a bold head line. Even "* 4." is treated as "* 1." and therefore "re-numbered" - so I chose to re-word it a little bit:)

[Former Member]

I had figured it was my use #4 (after I posted tht) but it only happens when # is the absolute first character on the line [shades of positional RunOff that]

[Former Member]

(similarily) Also figured out that my response was basically a rework of your #3 'dynamic' approach.

But it does sound like you know the territory well already. So you can pretty much ignore all my comments ... mute the moot as it were.

[Vlad]

After I read some articles in Internet, I got an impression that it is not simple to change the SQL standard implementation and add an optional value to TOP. In this comment, I want to tell you that you do not need to assign a max int value, and everything you need is to get the actual number of records. I hope the server retrieves this value fast and cheap. E.g.:

create or replace procedure "dba".STP_Test(in nMaxCount unsigned integer default null)
begin
    SET nMaxCount = ISNULL(nMaxCount, (SELECT COUNT(*) FROM sys.systab));
    SELECT TOP nMaxCount table_id, creator, table_name from sys.systab order by 1;
end;

[VolkerBarth]

Hi Vlad, thanks for the hint. In the simple sample, counting the rows is certainly an alternative. However, in my real queries, that would be difficult as these are rather complex (and I do not want to "repeat" the query code, see my original option 2) and I guess it would be rather inefficient as the query would have to be executed basically twice - that's why I had mentioned "or even worse: counting the rows befrorehand" to option 1.

---

Note, I do not attempt to change the SQL standard - what I ask to consider is whether the TOP clause as a vendor extension might be enhanced. As to the docs, "The row limitation clause is a vendor extension."