SQL question with Autocomplete

[mazdotnet]

Hi,

I'm working on a little autocomplete functionality with the AJAX
toolkit. I like to the following,

Take the following 2 SQLs
SELECT NAME FROM COUNTRIES WHERE NAME LIKE '%TORONTO%' (would return
false)
SELECT NAME FROM CITIES WHERE NAME LIKE '%TORONTO%' (would return
true)

I like to have a stored procedure that does this. How do you write the
IF condition. Bascially I want it to first check the countries table
and if not matches there , next go to the CITIES table and check it
there? or is there a way to check both tables at once and return only
a single resultset combining both matches in COUNTRIES and CITIES
table? (in a single column 'Name'?)

Thank you
M.

 

[Aidy]

Not really sure what you're after, but have a look at this;

IF EXISTS (SELECT 1 FROM Countries WHERE Name LIKE '%Toronto%')
SELECT 1
ELSE IF EXISTS (SELECT 1 FROM Cities WHERE Name LIKE '%Toronto%')
SELECT 1
ELSE
SELECT 0

 

[Eliyahu Goldin]

a single resultset combining both matches in COUNTRIES and CITIES

table? (in a single column 'Name'?)

SELECT NAME FROM COUNTRIES WHERE NAME LIKE '%TORONTO%'
UNION
SELECT NAME FROM CITIES WHERE NAME LIKE '%TORONTO%'


--
Eliyahu Goldin,
Software Developer
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net