T-SQL A problem with SELECT TOP (case [...])

Question

I have query like that:

(as You see I'd like to retrieve 50% of total rows or first 100 rows etc)

//@AllRowsSelectType is INT

 SELECT TOP (
    case @AllRowsSelectType
        when 1 then 100 PERCENT 
        when 2 then 50 PERCENT
        when 3 then 25 PERCENT
        when 4 then 33 PERCENT
        when 5 then 50
        when 6 then 100
        when 7 then 200 
    end
            ) ROW_NUMBER() OVER(ORDER BY [id]) AS row_num, a,b,c etc

why have I the error : "Incorrect syntax near the keyword 'PERCENT'." on line "when 1 [...]"

Answer 1

The syntax for TOP is:

TOP (expression) [PERCENT]
[ WITH TIES ]

The reserved keyword PERCENT cannot be included in the expression. Instead you can run two different queries: one for when you want PERCENT and another for when you don't.

If you need this to be one query you can run both queries and use UNION ALL to combine the results:

SELECT TOP (
    CASE @AllRowsSelectType
        WHEN 1 THEN 100
        WHEN 2 THEN 50
        WHEN 3 THEN 25
        WHEN 4 THEN 33 
        ELSE 0
    END) PERCENT
    ROW_NUMBER() OVER(ORDER BY [id]) AS row_num, a, b, c, ...
UNION ALL
SELECT TOP (
    CASE @AllRowsSelectType
        WHEN 5 THEN 50
        WHEN 6 THEN 100
        WHEN 7 THEN 200
        ELSE 0
    END)
    ROW_NUMBER() OVER(ORDER BY [id]) AS row_num, a, b, c, ...

Answer 2

You're also mixing two different types of use. The other is.

DECLARE @ROW_LIMT int

IF @AllRowsSelectType < 5
    SELECT @ROW_LIMIT = COUNT(*)/@AllRowsSelectType FROM myTable -- 100%, 50%, 33%, 25%
ELSE
    SELECT @ROW_LIMIT = 50 * POWER(2, @AllRowsSelectType - 5) -- 50, 100, 200...

WITH OrderedMyTable
(
  select *, ROW_NUMBER() OVER (ORDER BY id) as rowNum
  FROM myTable
)
SELECT * FROM OrderedMyTable
WHERE rowNum <= @ROW_LIMIT

Answer 3

You could do:

select top (CASE @FilterType WHEN 2 THEN 50 WHEN 3 THEN 25 WHEN 4 THEN 33 ELSE 100 END) percent * from
(select top (CASE @FilterType WHEN 5 THEN 50 WHEN 6 THEN 100 WHEN 7 THEN 200 ELSE 2147483647 END) * from
<your query here>
) t
) t

Which may be easier to read.