Modifying this query so it searches based on search text?
Posted on 2011-09-23
I have two unrelated tables:
published_ads (with ID, title, email_address)
email_addresses (with ID, email_address)
I have a stored procedure with one parameter (@searchText) and this parameter is used for the following (oversimplified) query:
SELECT * FROM published_ads WHERE [title] like @searchText
This is what I would like to do:
if the string in @searchText is a record of table [email_addresses], then the query will search in [published_ads.email_address]. Otherwise, the query will search [published_ads.title].
In other words, let's say @searchText is 'myEmail@email.com' and this string is in table [email_addresses]. Then the query will look like:
SELECT * FROM published_ads WHERE [email_address] like '%myEmail@email.com%'
On the other hand, @searchText is 'shoes', which is not in table [email_addresses]. Then the query will look like:
SELECT * FROM published_ads WHERE [title] like '%shoes%'
Is this possible?