select TOP (all)

Question

declare @t int
set @t = 10
if (o = 'mmm') set @t = -1
select top(@t) * from table

What if I want generally it resulted with 10 rows, but rarely all of them.

I know I can do this through "SET ROWCOUNT". But is there some variable number, like -1, that causing TOP to result all elements.

Answer 1

The largest possible value that can be passed to TOP is 9223372036854775807 so you could just pass that.

Below I use the binary form for max signed bigint as it is easier to remember as long as you know the basic pattern and that bigint is 8 bytes.

declare @t bigint =  case when some_condition then 10 else  0x7fffffffffffffff end;

select top(@t) * 
From table

If you dont have an order by clause the top 10 will just be any 10 and optimisation dependant.

If you do have an order by clause to define the top 10 and an index to support it then the plan for the query above should be fine for either possible value.

If you don't have a supporting index and the plan shows a sort you should consider splitting into two queries.

Answer 2

im not sure I understand your question.

But if you sometimes want TOP and other times don't just use if / else construct:

if (condition)
  'send TOP
  SELECT TOP 10 Blah FROM...
else
  SELECT blah1, blah2 FROM...

Answer 3

You can use dynamic SQL (but I, personally, try to avoid dynamic SQL), where you create a string of the statement you want to run from conditions or parameters. There's also some good information here on how to do it without dynamic SQL:

https://web.archive.org/web/20150520123828/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-use-a-variable-in-a-top-clause-in-sql-server.html

Answer 4

declare @top bigint = NULL
declare @top_max_value bigint = 9223372036854775807
if (@top IS NULL) 
begin
set @top = @top_max_value
end

select top(@top) * 
from [YourTableName]