TSQL: query with optional join

Question

Hi

I have a search ui with 3 all optional search criteria. 2 of them are simple criteria for a where statement, that I should be able to solve with this: http://stackoverflow.com/questions/697671/stored-procedure-with-optional-where-parameters.

The last criterion is using full text search where I join the result from ContainsTable. Is there some trick I can use to put everything in one stored procedure? Or should I make two queries, one with the full text search and one without?

Edited: I should have put my query here as well, sorry here it is

select Table1.* from Table1
join
(
 select [Key], SUM(Rank) as Rank from
 (
  SELECT [Key], Rank*3 as Rank FROM Table1ShortSearch(@Keywords) union all
  SELECT [Key], Rank*2 as Rank FROM Table1LongSearch(@Keywords)
 ) as RankingTbl
 group by [Key]
) as r
on Table1.Id = r.[Key]

where ( @Status_Id Is Null Or Status_Id = @Status_Id )

order by r.Rank Desc

Thanks.

Answer 1

you can put everything in one stored procedure and have an IF statement - http://msdn.microsoft.com/en-us/library/ms182717.aspx

Answer 2

Original Answer:

You can use an EXISTS function like so:

Select ..
From ..
Where ( @Status_Id Is Null Or Status_Id = @Status_Id )
 And (@Date Is Null Or [Date] = @Date )
 And (@Criteria Is Null Or Exists(
         Select 1
         From ContainsTable(TableName, Column1,...,ColumnN, @Criteria..) As SearchTable1
         Where SearchTable1.PK = OuterTable.PK
         ) )

---

After question revision:

The revised query is of a completely different nature than the original query. In the original query, you simply wanted to return results from Table1 and additionally filter those results if @Keywords was not null. In this query, you are outputting to the SELECT clause the freetext ranking. What would display for the ranking if @Keywords was passed as null?

If freetext ranking is not needed and you simply want to return results if either of the searches on @Keywords finds something, then you would do something like:

Select ...
From Table1
Where ( @Status_Id Is Null Or Status_Id = @Status_Id )
    And ...
    And (
        @Keywords Is Null
        Or Exists   (
                    Select 1
                    From Table1ShortSearch(@Keywords) As T1
                    Where T1.Key = Table1.Key
                    )
        Or Exists   (           
                    Select 1
                    From Table1LongSearch(@Keywords) As T2
                    Where T2.Key = Table1.Key
                    )
        )

If you want to display the freetext ranking then your original query or perhaps a CTE would be the solution however you will need to use a Left Join to your subquery if you want to account for @Keywords being null. That would make your query read:

Select ...
From Table1
    Left Join   (
                 Select [Key], Sum(Rank) as Rank 
                 From   (
                        Select [Key], Rank*3 As Rank 
                        From Table1ShortSearch(@Keywords) 
                        Union All 
                        Select [Key], Rank*2 As Rank 
                        From Table1LongSearch(@Keywords)
                        ) As RankingTbl
                Group By [Key]
                ) as R
        On R.[Key] = Table1.Id
Where ( @Status_Id Is Null Or Status_Id = @Status_Id )
    And ...
    And ( @Keywords Is Null Or R.Key Is Not Null )
Order By R.Rank Desc