select subquery inside then of case when statement?

Question

Is there a way to run a select statement from a "then" in the sql server case/when statement? (I need to run subqueries from a then statement.) I cannot have it in the where statement.

select 
  case @Group 
    when 6500 then (select top 10 * from Table1)
    when 5450 then (select top 5 * from Table1)
    when 2010 then (select top 3 * from Table1)
    when 2000 then (select top 1 * from Table1)
    else 0 
  end as 'Report'

Answer 1

One option is to remove this from the query and do something like:

declare @Numrows int;
select @Numrows = (case @Group 
                        when 6500 then  10
                        when 5450 then 5
                        when 2010 then 3
                        when 2000 then 1
                        else 0
                   end);

select top(@NumRows) *
from Table1;

You could also do it this way:

with const as (
      select (case @Group 
                        when 6500 then  10
                        when 5450 then 5
                        when 2010 then 3
                        when 2000 then 1
                        else 0
                   end) as Numrows
    )
select t.*
from (select t.*, ROW_NUMBER() over () as seqnum
      from table1 t 
     ) t cross join
     const
where seqnum <= NumRows;

In this case, you need to list out the columns to avoid getting seqnum in the list.

By the way, normally when using top you should also have order by. Otherwise, the results are indeterminate.