开发者

Union and If Exists - not working together - Please help

I need to get dummy values if they do no rows returned from table. The If exists works by itself, but gives error with a Union. Can someone please guide me with a solution or a workaround?

create table test1 (col1 varchar(10))    
create table test2 (col1 varchar(10))    
create table test3 (col1 varchar(10))


insert test1 values ('test1-row1')    
insert test1 values ('test1-row2')    
insert test2 va开发者_Python百科lues ('test2-row1')    
insert test2 values ('test2-row2')

select col1 from test1    
union    
select col1 from test2    
union    
if exists (select * from test3)    
    select col1 from test3    
else    
    select 'dummy'


You could add another union that returns the dummy row if test3 is empty:

select col1 from test1    
union    
select col1 from test2    
union    
select col1 from test3    
union
select 'dummy' where not exists (select * from test3)


I believe that you cannot use an if like that. The IF statement is to control control flow, now data selection. Change the query to

IF exists (select * from test3)
BEGIN
--query all three
END
ELSE
BEGIN
--query just two
END
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜