Conditional UNION

[Andel]

Is there anyway I can use UNION only if it meets my condition? I'm trying to combine table_a and table_b (only if it meets my condition). Here's what I'm trying to do but it doesn't work.

declare @type varchar(10)
set @type = 'all' -- the value is either 'all' or null
select * from table_a
if @type = 'all' -- if 'all' then do union else no need
union all select * from table_b

please help!

Maingel




Andel
maingel@hotmail.com

 

[mfairchild]

This won't work if this is supposed to be a view but you could:

Create Procedure NewProcedureName
AS

SELECT TableA_Field1, TableA_Field2
INTO #Temp
FROM Table_A

INSERT INTO #Temp
SELECT TableB_Matching_Field1, TableB_Matching_Field2
FROM TableB
WHERE @Type = 'All'

SELECT * FROM #Temp

 

[TUANKIET]

You can use the dynamic statement as the following:

declare @type varchar(10)
declare @statement varchar(1000)

set @type = 'all' -- the value is either 'all' or null

select @statement = 'select * from table_a '

if @type = 'all' -- if 'all' then do union else no need
select @statement = @statement + ' union all select * from table_b'

Exec (@statement)