SQL Server FREETEXTTABLE with optional query on linked table
I'm trying to build a search stored procedure for my application. I'm
using sql server 2008. The search allows the user to query several fields
on the "Case" table along with a free text search on the "Case.Name". The
user can also perform a free text search on a linked table "Address".
Is it possible to make the second free text search optional, without
having 2 queries. I've actually got several fields users can search across
multiple linked tables so I'd have to write a statement for every possible
combination of fields.
In my stored proc I've got:
SELECT DISTINCT Cases.*, Case_ftt.Rank
FROM
dbo.Contacts INNER JOIN
dbo.CaseContacts ON dbo.Contacts.ContactID =
dbo.CaseContacts.ContactID RIGHT OUTER JOIN
dbo.Cases ON dbo.CaseContacts.CaseID = dbo.Cases.CaseID LEFT
OUTER JOIN
dbo.Addresses ON dbo.Cases.AddressID = dbo.Addresses.AddressID
-- CaseName
INNER JOIN
(
(SELECT [Key], [Rank] FROM FREETEXTTABLE(Cases, Name, @Name))
UNION
(SELECT CaseID as [Key], 0 as [Rank] FROM Cases WHERE
@Name='""')
) as Case_ftt on Case_ftt.[Key] = Cases.CaseID
-- Address
INNER JOIN
FREETEXTTABLE(Addresses, *, @Address)as Address_ftt
on Address_ftt.[Key] = Cases.AddressID
WHERE
(CaseStatusID = @CaseStatusID OR @CaseStatusID IS NULL) AND
(CaseTypeID = @CaseTypeID OR @CaseTypeID IS NULL) AND
CaseStatusID <> 'VOID'
ORDER BY
Case_ftt.[Rank] DESC
OPTION (RECOMPILE)
With the Case freetexttable used the union to make it optional, which
works as expected but the same does not work for the address field as not
all cases are linked to addresses so null address are not included in the
result set.
No comments:
Post a Comment