select subquery inside then of case when statement?

case, sql, sql-server-2008, subquery

Solution

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.

Problem

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' ```

Original source