[求助]求一存储过程中SQL语句的写法，谢谢！

[renchao]

在存储过程中有一参数@BZ，
当@BZ=Y时，SELECT @BZ AS BZ, A.ID AS ID, A.A1 AS F FORM A WHERE 条件1；结果如：
Y 2 ABC
Y 5 KDG
Y 3 GFH
当@BZ=N时，SELECT @BZ AS BZ, B.ID AS ID, B.B1 AS F FORM B WHERE 条件2；结果如：
N 1 GF
N 5 GFGG
N 7 GTFH
当@BZ=A时，既查@BZ=1的情况，又查@BZ=2的情况，但在BZ字段加以区分。结果如：
Y 2 ABC
Y 5 KDG
Y 3 GFH
N 1 GF
N 5 GFGG
N 7 GTFH

请问应该写这个SQL语句？请指导，谢谢！

[jiushaoye]

declare @sql nvarchar(2000)
select @sql = case when @bz = 'y' then 'SELECT @BZ AS BZ, A.ID AS ID, A.A1 AS F FORM A WHERE 条件1'
when @bz = 'n' then 'SELECT @BZ AS BZ, B.ID AS ID, B.B1 AS F FORM B WHERE 条件2
when @bz = 'a' then 'SELECT @BZ AS BZ, B.ID AS ID, B.B1 AS F FORM B WHERE 条件2 union all select @BZ AS BZ, A.ID AS ID, A.A1 AS F FORM A WHERE 条件1'
else ''
end
if @sql <> ''
exec(@sql)

[renchao]

case when ... then ...是标准的SQL语句吗？
没见过...

[-狙击手-]

if @BZ=Y SELECT @BZ AS BZ, A.ID AS ID, A.A1 AS F FORM A WHERE 条件1
if @BZ=N SELECT @BZ AS BZ, B.ID AS ID, B.B1 AS F FORM B WHERE 条件2
if @BZ=A
SELECT @BZ AS BZ, B.ID AS ID, B.B1 AS F FORM B WHERE 条件2 union all
SELECT @BZ AS BZ, A.ID AS ID, A.A1 AS F FORM A WHERE 条件1

[yinwun]

用if吧。效率高点。
happyflystone(仙林幽谷客)
那个就行了/。

[renchao]

但当@BZ='A'时，上面的方法没办法在查询出的BZ字段标注出'Y'还是'N'来啊？
只能是这样的结果：
A 2 ABC
A 5 KDG
A 1 GF
A 5 GFGG

应该是这样：
Y 2 ABC
Y 5 KDG
N 1 GF
N 5 GFGG

[renchao]

没人会吗？请多多帮忙啊，谢谢了

[mislrb]

呵呵，你的问题交待的不清楚

当@BZ='A'时，什么时候设为Y，什么时候设为N，

[renchao]

@BZ='A'是满足@BZ='Y'或@BZ='B'的两种结果的UNION，

当满足@BZ='Y'时查出的结果，应设BZ字段为'Y',
当满足@BZ='N'时查出的结果，应设BZ字段为'N'。

具体到这个例子，就是从A表中查出的结果，设标志位BZ为'Y',
从B表中查出的结果，设标志位BZ为'N',有办法吗？

[renchao]

沒人知道嗎？

[powerpretty]

declare @sql nvarchar(2000)
select @sql = case when @bz = 'y' then 'SELECT @BZ AS BZ, A.ID AS ID, A.A1 AS F FORM A WHERE 条件1'
when @bz = 'n' then 'SELECT @BZ AS BZ, B.ID AS ID, B.B1 AS F FORM B WHERE 条件2
when @bz = 'a' then 'SELECT @BZ AS BZ, B.ID AS ID, B.B1 AS F FORM B WHERE 条件2 union all select @BZ AS BZ, A.ID AS ID, A.A1 AS F FORM A WHERE 条件1'
else ''
end
if @sql <> ''
exec(@sql)
我认为是对的
在sql里是有这个语法的

如果你不看好可以用if 呀


declare @sql nvarchar(2000)
if @sql='Y'
SELECT @BZ AS BZ, A.ID AS ID, A.A1 AS F FORM A WHERE 条件1
if @sql='N'
SELECT @BZ AS BZ, B.ID AS ID, B.B1 AS F FORM B WHERE 条件2
if @sql='a'
SELECT @BZ AS BZ, B.ID AS ID, B.B1 AS F FORM B WHERE 条件1 or 条件2

[ReViSion]

直接写不就得了呀，你自己都说得很明白啦
set @BZ=upper(@bz)
if @BZ='Y'
SELECT @BZ AS BZ, A.ID AS ID, A.A1 AS F FORM A WHERE 条件1
if @sql='N'
SELECT @BZ AS BZ, B.ID AS ID, B.B1 AS F FORM B WHERE 条件2
if @sql='A'
SELECT 'Y' AS BZ, A.ID AS ID, A.A1 AS F FORM A WHERE 条件1
union all
SELECT 'N' AS BZ, B.ID AS ID, B.B1 AS F FORM B WHERE 条件2

--------------------------------------------------
这样就足够啦