Inner join only if condition is met

Transact-SQL
1

hi,
i have a dilema. is there solution to make INNER JOIN only if statement is met?
i put some of the code for explination

declare @param1 int
set @param1= (SELECT count(distinct Field1) FROM SomeTable2)

select * from SomeTable1
if pUXP>0
inner join SomeTable2
else
none

2

can you try this?

if puxp>o
select * from sometable1
else
select * from sometable1
inner join SomeTable2

3

thank you.
i know but unfortunately my code is complex and i want to avoid repeating the code

4

Can put your code in a stored procedure. Make two copies of it, call from a parent SP.

IF pupx > 0
Exec proc1
ELSE
Exec Proc2

5

If SomeTable2 always exists, you can do this:

select *
from SomeTable1 st1
inner join SomeTable2 st2 on st1.pUXP > 0 AND st2.key_col = st1.key_col

6

thank you
i did this but is not working

HERE IS THE EXEMPLE AGAIN:

declare @pRole as int
set @pRole = (SELECT RoleId FROM tbl_SLA_User_Roles WHERE UserAccount = @pUserAccount)

if @pRole<> 1 --> I want INNER JOIN
else --> I don not want JOIN

7

yes i can but that code is more complex and became redundant

8

Any attempt at a conditional join will mess up the query plan.
Try putting your complex code in a view:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE VIEW someview
AS
SELECT *
FROM sometable1;
GO

You can then use:

SELECT *
FROM someview;

or

SELECT *
FROM someview V
    JOIN sometable2 T
        ON V.col = T.col;

etc

9

ahhh ok. thank you.
i will try this or like other suggestion to split in two SP and a SP parent.

10

You should have two child SPs and a parent SP to help stop re-compiles.

11

yes. thank you