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
can you try this?
if puxp>o
select * from sometable1
else
select * from sometable1
inner join SomeTable2
thank you.
i know but unfortunately my code is complex and i want to avoid repeating the code
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
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
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
yes i can but that code is more complex and became redundant
Ifor
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
ahhh ok. thank you.
i will try this or like other suggestion to split in two SP and a SP parent.
Ifor
10
You should have two child SPs and a parent SP to help stop re-compiles.