CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Abdul Rasheed
Abdul Rasheed

Reputation: 6709

Dynamic TOP N / TOP 100 PERCENT in a single query based on condition

A local variable @V_COUNT INT. If the variable @V_COUNT is '0'(zero) the return all the records from table otherwise return the number of {@V_COUNT} records from table. For example if @V_COUNT = 50, return TOP 50 records. If @V_COUNT is 0 then return TOP 100 PERCENT records. Can we achieve this in a single query?

Sample query :

DECLARE @V_COUNT INT = 0

SELECT  TOP (CASE WHEN @V_COUNT > 0 THEN  @V_COUNT ELSE  100 PERCENT END) *
FROM    MY_TABLE
ORDER BY COL1

Incorrect syntax near the keyword 'percent'

Upvotes: 0

Views: 1637

Answers (5)

Ramapriyan C
Ramapriyan C

Reputation: 67

DECLARE @V_COUNT int = 3
select * 
from 
  MY_TABLE 
ORDER BY 
Service_Id asc  
offset case when @V_COUNT >0 then  ((select count(*) from MY_TABLE)- @V_COUNT) else @V_COUNT end rows

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

A better solution would be to not use TOP at all - but ROWCOUNT instead:

SET ROWCOUNT stops processing after the specified number of rows.
...
To return all rows, set ROWCOUNT to 0.

Please note that ROWCOUNT is recommended to use only with select statements -

Important
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax.

DECLARE @V_COUNT INT = 0

SET ROWCOUNT @V_COUNT -- 0 means return all rows...

SELECT   *
FROM    MY_TABLE
ORDER BY COL1

SET ROWCOUNT 0 -- Avoid side effects...

This will eliminate the need to know how many rows there are in the table

Be sure to re-set the ROWCOUNT back to 0 after the query, to avoid side effects (Good point by Shnugo in the comments).

Upvotes: 3

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

SET ROWCOUNT forces you into procedural logic. Furthermore, you'll have to provide an absolute number. PERCENT would need some kind of computation...

You might try this:

DECLARE @Percent FLOAT = 50;

SELECT  TOP (SELECT CAST(CAST((SELECT COUNT(*) FROM sys.objects) AS FLOAT)/100.0 * CASE WHEN @Percent=0 THEN 100 ELSE @Percent END +1 AS INT))  o.*
FROM    sys.objects o
ORDER BY o.[name];

This looks a bit clumsy, but the computation will be done once within microseconds...

Upvotes: -1

HoneyBadger
HoneyBadger

Reputation: 15140

You can do something like: 

DECLARE @V_COUNT INT = 0

SELECT  TOP (CASE WHEN @V_COUNT > 0 THEN  @V_COUNT ELSE (SELECT COUNT(1) FROM MY_TABLE) END) *
FROM    MY_TABLE

Upvotes: 1

Limonka
Limonka

Reputation: 846

Instead of 100 percent you can write some very big number, which will surely be bigger than possible number of rows returned by the query, eg. max int which is 2147483647.

Upvotes: 1

Related Questions