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
精彩评论