November 3, 2009 at 1:56 am
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!
November 3, 2009 at 2:05 am
You need to do a catch all query
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
November 3, 2009 at 2:12 am
It tells about dynamic sql which I do not want to use
November 3, 2009 at 2:25 am
This article will explain you options
November 3, 2009 at 6:13 am
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
November 3, 2009 at 8:11 am
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
November 3, 2009 at 9:24 pm
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.
November 3, 2009 at 11:43 pm
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.
November 4, 2009 at 1:12 am
did you read this article ?
http://www.sommarskog.se/dyn-search-2005.html
You wont get any better response than the information there.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply