conditional inner join

Question

Post reply

conditional inner join

deepakaggar

Old Hand

Points: 399
More actions
November 3, 2009 at 1:56 am

#136770

Hi,
How can I make an inner join execute only if required? (I know we can do a left join instead, but that has overhead)
For example,
I have three tables
USER - UserID, UserName (master table)
STATUS - StatusID, StatusName (master table)
USER_STATUS - UserID, StatusID (association table)
Now, I have a search procedure
create procedure SearchUsers (@intStatusID int = 0)
begin
/*I want something like
select * from USER
If (@intStatusID > 0)
inner join USER_STATUS us on us.ACE_ID = USER.statusID AND us.StatusID = @intStatusID */
end
I tried for CASE statements too. But it didn't work. Please suggest!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

You need to do a catch all query

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Clear Sky SQL
My Blog[/url]

deepakaggar Old Hand Points: 399 More actions · Answer 2

It tells about dynamic sql which I do not want to use

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

This article will explain you options

http://www.sommarskog.se/dyn-search-2005.html

Clear Sky SQL
My Blog[/url]

HowardW One Orange Chip Points: 29109 More actions · Answer 4

You don't need to use dynamic sql. The easiest way to do this would be to have two select statements and an if else to decide which one to run. E.g.

create procedure SearchUsers (@intStatusID int = 0) as

begin

If (@intStatusID > 0)

Begin

select * from

inner join USER_STATUS us on us.ACE_ID = .statusID AND us.StatusID = @intStatusID

End

else

Begin

select * from

End

end

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

HowardW (11/3/2009)
You don't need to use dynamic sql. The easiest way to do this would be to have two select statements and an if else to decide which one to run.

Yes, but watch the performance implication

http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aravind.s SSC-Addicted Points: 449 More actions · Answer 6

the kind of functionality that You are looking for doesnt exist in sql 2005.The various ways in which it can be achieved has been given by the members who have replied to ur post.There will always be performance implications for these sort of freak requirements.Weigh ur options well with the data that u have now and also project the future data flow as well, before taking any decisions.

deepakaggar Old Hand Points: 399 More actions · Answer 7

You don't need to use dynamic sql. The easiest way to do this would be to have two select statements and an if else to decide which one to run. E.g.
create procedure SearchUsers (@intStatusID int = 0) as
begin
If (@intStatusID > 0)
Begin
select * from
inner join USER_STATUS us on us.ACE_ID = .statusID AND us.StatusID = @intStatusID
End
else
Begin
select * from
End
end

This option is good when you have just one or twp parameters. I am looking for a more general approach. In your example, if I have 3 parameters I will need to make 9 combination of if then statements.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 8

did you read this article ?

http://www.sommarskog.se/dyn-search-2005.html

You wont get any better response than the information there.

Clear Sky SQL
My Blog[/url]