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.

If you want all the matches (i.e. no short-circuiting - check all the
tables) then UNION / UNION ALL are your friend; just lob UNION between your
two SELECTs and you're done - but make sure you keep the column types
compatible in each select (SQL Server 2005 gets very upset otherwise)

If you want to short-circuit (i.e. if any contries are found, don't check
cities) then you would be best to create a holding-pen (perhaps DECLARE
@names TABLE ([Name] varchar(100) NOT NULL)) and then do an INSERT @names
([Name]) SELECT... and check the @@ROWCOUNT (or check for rows in @names).
If no rows were inserted, then run the next INSERT/SELECT etc. Finally,
SELECT [Name] FROM @names ORDER BY [Name]

Marc