select TOP (all)

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.

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.

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

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

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]

a dynamic sql version isn't that's hard to do.

CREATE PROCEDURE [dbo].[VariableTopSelect] 
    -- Add the parameters for the stored procedure here
    @t int

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

declare @sql nvarchar(max)

if (@t=10)
begin
    set @sql='select top (10) * from table'
end
else
begin
    set @sql='select * from  table'
end

exec sp_executesql @sql


END

with this sp, if they send 10 to the sp, it'll select the top 10, otherwise it'll select all.

The best solution I've found is to select the needed columns with all of your conditions into a temporary table, then do your conditional top:

DECLARE @TempTable TABLE(cols...)

INSERT INTO @TempTable
SELECT blah FROM ...

if (condition)
 SELECT TOP 10 * FROM @tempTable
else
 SELECT * FROM @tempTable

This way you follow DRY, get your conditional TOP, and are just as easy to read.

Cheers.

It is also possible with a UNION and a parameter

SELECT DISTINCT TOP 10
Column1, Column2
FROM Table
WHERE @ShowAllResults = 0
UNION 
SELECT DISTINCT 
Column1, Column2
FROM Table
WHERE @ShowAllResults = 1

I might be too late now, or getting too old But I solved that by using Top(100)Percent

This goes around all complexities

Select Top(100)Percent * from tablename;

Use the statement "SET ROWCOUNT @recordCount" at the beginning of the result query.The variable "@recordCount" can be any positive integer. It should be 0 to return all records.

that means , "SET ROWCOUNT 0" will return all records and "SET ROWCOUNT 15" will return only the TOP 15 rows of result set.

Drawback can be the Performance hit when dealing with large number of records. Also the SET ROWCOUNT will be effective throughout the scope of execution of the whole query.