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"... |
?:-/ not certain why that last post is shouting at you .... ?:-/
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.
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:)
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]
(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.
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.:
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."