CODEWITHSUNDEEP
sqlsql-server-2008subquerycase
Rainhider
Rainhider

Reputation: 836

select subquery inside then of case when statement?

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'

Upvotes: 9

Views: 56094

Answers (3)

ljh
ljh

Reputation: 2594

@Gordon has the answer already. This is just second opnion. You can use 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)

Upvotes: 1

Young Bob
Young Bob

Reputation: 743

You can't have a SELECT within a SELECT. You can use IF...ELSE though e.g.

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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.

Upvotes: 3

Related Questions