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 somethi开发者_JS百科ng like this?

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

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

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

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

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)