CODEWITHSUNDEEP
sqlsql-server
user30410
user30410

Reputation: 131

Select All records or top dynamic number of records in SQL Server

I have a query like

DECLARE @TOP VARCHAR(10) = '10'

I need to select top @top values from a table like

I had used

select top (convert(int,@top)) * from <table>

but my problem is the @top value may have the value 'ALL' also. That time this query is not possible. Please suggest a query to return result whatever the value for @top (either 'all' or value)

Upvotes: 1

Views: 2728

Answers (5)

Ranadeera Kantirava
Ranadeera Kantirava

Reputation: 324

try this

DECLARE @TOP NVARCHAR(100) = 'ALL'
IF @TOP = 'ALL' SET @TOP = '100 PERCENT '
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT TOP '+@TOP+' * FROM table_name' 
EXEC(@SQL)

Upvotes: 1

Karl Kieninger
Karl Kieninger

Reputation: 9129

Similar to @Veljasije's answer, but avoid using an arbitrary number for all. If you know your data you should be fine picking a large number as he suggests.

DECLARE @top VARCHAR(10) = 'all'
SELECT TOP (CASE @top 
                 WHEN 'all' THEN (SELECT COUNT(*) FROM Orders) 
                 ELSE CAST(@top AS BIGINT) 
            END) 
       * 
  FROM Orders

However, I like the idea of using max INT, but TOP takes a BIGINT so you want that. I tried:

DECLARE @top VARCHAR(10) = 'all'
SELECT TOP (CASE @top 
                 WHEN 'all' THEN 9223372036854775807 
                 ELSE CAST(@top AS BIGINT) 
            END) 
       * 
  FROM Orders

And that unexpectedly errors:

Msg 1060, Level 15, State 1, Line 34 The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer

However this work:

SELECT TOP (9223372036854775807) * FROM Orders

So I'm confused. If anyone can provide insight here, great, otherwise I may open a question on that.

Upvotes: 0

veljasije
veljasije

Reputation: 7092

Maybe this solution can help ypu:

declare @top varchar(10) 
set @top = 'all'

select top(case @top when 'all' then 1000000 else cast(@top as int) end)
    *
from MyTable

Value 1000000 is random value, you can set maximum integer value to sure to get all rows.

Upvotes: 1

Kevin Hogg
Kevin Hogg

Reputation: 1781

To avoid extra complication, run one of two queries:

IF @Top = 'All'
BEGIN
     SELECT *
       FROM <table>
END
ELSE
BEGIN
     SELECT TOP CAST(@Top AS INT) *
       FROM <table>
END

It's a small overhead in typing text that make the code easier to read in the future.

Dynamic queries are useful if there is no other way, but explicit queries are better for future understanding and in performance terms (can't determine execution plan until query is constructed).

A slightly better way would be to use an INT variable, were 0 indicates ALL, and there's no requirement to CAST/CONVERT the variable.

Upvotes: 2

Szymon
Szymon

Reputation: 43023

You can do that using dynamic SQL:

DECLARE @TOP NVARCHAR(10)= N'10'

declare @sql as nvarchar(max) = N'select '
if @top <> N'ALL'
    set @sql = @sql + N'top ' + @top
set @sql = @sql + N' * from table1'

exec sp_executesql @sql

Upvotes: 1

Related Questions