CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2012-express
user2977985
user2977985

Reputation:

Control JOIN type between two tables based on parameter value

This is what I would like to do using SQL Server:

declare @isclosed int
set @isclosed=0
declare @jointype varchar(50)

if(isclosed=0)
begin
set @jointype ='left outer join'
end

else
begin
set @jointype ='right outer join'
end


select * from #table1 @jointype  #table2
union
select * from table3

How can I use the parameter to do the JOIN?

Upvotes: 0

Views: 281

Answers (2)

Ann L.
Ann L.

Reputation: 13975

You might be able to make something like this work:

declare @isclosed int
set @isclosed=0

-- Since @isclosed can't be equal to 0 and at the same time not equal to 0,
-- only one of the following queries (the ones UNION ALL'd together) will return
-- records.

select * 
from   #table1 
     left outer join  
       #table2 on [whatever]
where (@isclosed=0)
union all 
select *
from   #table1
     right outer join
       #table2 on [whatever]
where (@isclosed <> 0)

union
select * 
from   table3

Another option would be to experiment with FULL OUTER JOIN and filtering for NULLs on the keys of #table1 or #table2 depending on the value of @isclosed.

Upvotes: 0

AHiggins
AHiggins

Reputation: 7227

You're looking for something like this ... given your structure (bit value controls what's being executed) you probably don't have to worry about SQL Injection.

declare @isclosed int
set @isclosed=0
declare @jointype varchar(50)

if(@isclosed=0)
  begin
    set @jointype ='left outer join'
  end

else
  begin
    set @jointype ='right outer join'
  end

DECLARE @SQL VARCHAR(4000)
SET @SQL = 

'
SELECT *
FROM
    #table1 
     ' + @jointype  + '
    #table2 ON 
        -- add something here as your JOIN condition

UNION

SELECT * 
FROM table3
'

EXECUTE sp_executesql @SQL

Upvotes: 1

Related Questions