Select a subquery inside the case when the operator?

Question

Is there a way to run the select statement from "then" in the case / when statement SQL file? (I need to run subqueries from the 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 request 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 can also do it like this:

 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 specify the columns to avoid getting seqnum in the list.

By the way, usually when using top you should also have order by . Otherwise, the results will be uncertain.

Answer 2

You cannot have a SELECT in a SELECT. You can use IF ... ELSE, although, for example,

 IF @Group = 6500 select top 10* from Table1 AS Report ELSE IF @Group = 5450 select top 5* from Table1 AS Report ELSE IF @Group = 2010 select top 3* from Table1 AS Report ELSE IF @Group = 2000 select top 1* from Table1 AS Report 

Answer 3

@ Gordon already has an answer. This is just a second guess. You can use a dynamic query.

 declare @query varchar(max) declare @Group int set @query = '' if @Group = 6500 set @query = 'select top 10 * from table1' if @Group = 5450 set @query = 'select top 5 * from table1' if @Group = 2010 set @query = 'select top 3 * from table1' if @Group = 2000 set @query = 'select top 1 * from table1' exec(@query)