求一SQL语句。在线.....

[bondhu]

我想实现如下功能：
declare @flag char(1) --在运行时副职

select a.id,a.name
from test a
where a.type = ( case @flag when 'A' then 'a' or 'c' when 'B' then 'b'or 'd' end )

上述语法是错的，主要是最后的case语句有误。我的目的是，如果@flag = 'A'则检索type = 'a'or 'c'的记录。如果@flag = 'B'则检索type = 'b'or 'd'的记录。
如何实现呢？

[bondhu]

不好意思，我将问题简化过度了，实际情况是这样的
CREATE PROCEDURE u_import_tritem
@dates datetime,
@datee datetime,
@movetype char(1), --in or out
@product char(1) --product or material

AS
select a.ref_no,a.move_date,a.tran_type,a.flow_type,a.move_type,a.issued_to,b.item_no,b.qty,
c.item_no,c.article_code,c.description
into #tt1
from icmovh a join icmovd b
on a.ref_no = b.ref_no join gbitem c on b.item_no = c.item_no

where a.move_date >= @dates and a.move_date <= @datee
and a.flow_type = ( case @product when 'A' then 'i' when 'B' then 'r'end )
and c.article_code is not null
and c.article_code <> ''
and a.posted = 'Y'
and a.move_type = ( case @product when 'B' then 'decin' or'bomrtn' when 'A' then 'decout'or mkout' or 'prdmsy'or 'prdout' end ) --就是这里错误

select * into #tt2 from dbo.trade e
where e.typeflag = @product

select a.ref_no,b.haiguanid,b.itemname,a.qty,c.oriprice,a.movedate,@movetype,@product
from #tt1 a join #tt2 b on a.article_code = b.article_code

当然还是那句case语句的问题。因为case在where 中所以楼上的部分方法用不上。因为a.move_type的值不子一个字符，所以大力的方法也用不上。aierong兄的应该可行，可不大方便，我再加100分，请兄台们想想如何解决。

[LoveSQL]

and a.move_type = ( case @product when 'B' then 'decin' or'bomrtn' when 'A' then 'decout'or mkout' or 'prdmsy'or 'prdout' end ) --就是这里错误
========================================================================
把这句话改成下面这样，看看可以吗？
and a.move_type in(
select case @product when 'B' then 'decin' end union
select case @product when 'B' then 'bomrtn' end union
select case @product when 'A' then 'decout' end union
select case @product when 'A' then 'mkout' end union
select case @product when 'A' then 'prdmsy' end union
select case @product when 'A' then 'prdout' end )

[pengdali]

declare @flag char(1) --在运行时副职

select a.id,a.name
from test a
where a.type like case @flag when 'A' then '[ac]' when 'B' then '[bd]' end

[txlicenhe]

if @flag = 'A'
select a.id,a.name
from test a
where a.type in ('a', 'c')
if @flag = 'B'
select a.id,a.name
from test a
where a.type in('a','c')

[aierong]

select a.id,a.name
from test a
where a.type in ( case @flag when 'A' then in('a','c')
when 'B' then in('b','d')
end )

[sdhdy]

declare @flag char(1) --在运行时副职
if @flag='A'
select a.id,a.name from test a where a.type in ('a','c')
else
if @flag='B'
select a.id,a.name from test a where a.type in ('b','d')

[aierong]

上面错误


declare @flag char(1) ,@sql nvarchar(100)
select @flag='a'
select @sql='select a.id,a.name from test a where a.type in ( '+
( case @flag when 'a' then '''a'',''c'''
when 'b' then '''b'',''d'''
end )+')'
print @sql
execute sp_executesql @sql