• Although your solution may work perfectly well, you are not thinking about scale.

    What happens when you get more than 10 rows ?

    Compare your solution to a simple select as show below

    drop table city

    go

    Create TABLE city(CityID int identity Primary key,CityName varchar(100),AlternateCityName1 varchar(100),AlternateCityName2 varchar(100))

    go

    Create index idxName on City(CityName)

    go

    Create index idxAlternate on City(AlternateCityName1) where AlternateCityName1 is not null

    go

    Create index idxAlternate2 on City(AlternateCityName2) where AlternateCityName2 is not null

    go

    INSERT INTO City(CityName,AlternateCityName1,AlternateCityName2)

    select city,city,city from AdventureWorks2012.Person.Address

    go

    set statistics io on

    go

    Select * from City

    where CityName like 'Mad%'

    or AlternateCityName1 like 'Mad%'

    or AlternateCityName2 like 'Mad%'

    go

    DECLARE @SearchString varchar(20) = 'mad'

    ;WITH City_CTE(CityID,Name) AS (

    SELECT

    CityID,

    'Name' = CASE

    WHEN CityName like (@SearchString + '%') THEN CityName

    WHEN AlternateCityName1 like (@SearchString + '%') THEN AlternateCityName1

    WHEN AlternateCityName2 like (@SearchString + '%') THEN AlternateCityName2

    END

    FROM City )

    SELECT * FROM City_CTE WHERE Name IS NOT NULL



    Clear Sky SQL
    My Blog[/url]