The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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)
   -- 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;

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

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

  • Option 4: Build the SQL string conditionally on the nullability of your optional parameter and use Execute Immediate maybe?
(22 Oct '15, 10:09) Nick Elson S...
Replies hidden

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

(22 Oct '15, 10:23) Nick Elson S...
Replies hidden

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.

(22 Oct '15, 10:24) Volker Barth

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:)

(22 Oct '15, 10:39) Volker Barth

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]

(22 Oct '15, 10:51) Nick Elson S...

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

(22 Oct '15, 10:53) Nick Elson S...

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)
    SET nMaxCount = ISNULL(nMaxCount, (SELECT COUNT(*) FROM sys.systab));
    SELECT TOP nMaxCount table_id, creator, table_name from sys.systab order by 1;
(23 Oct '15, 03:43) Vlad
Replies hidden

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

(23 Oct '15, 05:18) Volker Barth
showing 5 of 8 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 22 Oct '15, 09:31

question was seen: 1,949 times

last updated: 23 Oct '15, 05:18