CODEWITHSUNDEEP
sql-server-2008case
Rauf
Rauf

Reputation: 12852

sql server case

I want to select from different tables based on a parameter called 'type'. Can i use 'CASE' to select the from table?

Can i use something like this?

select 
a as abc
b as xyz
from ( CASE when @type = 1 then tblSales
            when @type = 2 then tblTransfer
       end
)

Upvotes: 0

Views: 104

Answers (4)

ashish.chotalia
ashish.chotalia

Reputation: 3746

Declare @Sql nvarchar(4000)
Declare @type   int
SET @type = 1

SET @Sql = ''

set @Sql = @Sql + '
    select * FROM '
IF @type = 1
BEGIN
    set @Sql = @Sql + '[tblSales]'
END
ELSE
BEGIN
    set @Sql = @Sql + 'tblTransfer'
END

print @sql
exec (@Sql)

Upvotes: 1

Roman
Roman

Reputation: 276

Is this what you are searching for?

select
    a as abc,
    b as xyz
from 
    tblSales
WHERE
    @type = 1
UNION
select
    a as abc,
    b as xyz
FROM 
    tblTransfer
WHERE
    @type = 2

Upvotes: 2

JNadal
JNadal

Reputation: 408

You can try a union if you want to do that (example below), or you can use a simple 'if' statement.

You cannot use a case statement there, because a case returns a single value, rather than a table (which is what the "from" expects).

create table #tblsales
(
    a varchar(1),
    b varchar(1)
)

create table #tblTransfer
(
    a varchar(1),
    b varchar(1)
)

insert into #tblSales(a,b) values ('s','1')
insert into #tblSales(a,b) values ('s','2')

insert into #tblTransfer(a,b) values ('t','1')
insert into #tblTransfer(a,b) values ('t','2')

declare @type int
set @type=1

select a as abc, b as xyz
from
    (
    select a,b,thetype
    from
        (select a,b, 1 as thetype from #tblsales) sales
        union
        (select a,b, 2 as theType from #tblTransfer)
    ) joined
where theType=@type

Upvotes: 1

jfollas
jfollas

Reputation: 1235

I'd probably do something like:

SELECT a AS abc, b AS xyz FROM tblSales WHERE @type=1
UNION ALL
SELECT a AS abc, b AS xyz FROM tblTransfer WHERE @type=2

Upvotes: 4

Related Questions