Wildcards and Variables in a Stored Proc

[JefB]

I have an Airline table in SQL2K.

The fields, all varchar(50), are: AirlineID, AirlineName and AirportsServed.

AirportsServed contains 1 or more airport codes: i.e. "ORD,CLE,DFW,DCA,LAX"

I need an SP that will return all airlines serving a given airport. In other words; show all airlines with "DCA" somewhere in the AirportsServed field.

I have tried this:

CREATE PROCEDURE [dbo].[AirlineLookup]

@ASrvd varchar
AS

SELECT DISTINCT AirlineID, AirlineName, AirportsServed
FROM dbo.Airlines
WHERE patindex('%' + @ASrvd + '%', AirportsServed) >0
GO


The results are every record in which the letter "C" is anywhere in the AirportsServed field.

I also tried:

CREATE PROCEDURE [dbo].[AirlineLookup]

@ASrvd varchar
AS

SELECT DISTINCT AirlineID, AirlineName, AirportsServed
FROM dbo.Airlines
WHERE (AirportsServed like '%' + @ASrvd +'%')
GO


The same result.
I also tried using "charindex" (instead of "patindex&quot.

How do I get a variable (@ASrvd) to work with wildcards ('%') or look at a complete pattern?

JefB

 

[swampBoogie]

My advice is to redesign the database and store each value as one record in a separate table.

With the design you have you can never get any efficiency as it is not possible to use indexes for your queries. The code will also be complex and thus error prone.

 

[SQLSister]

I have to agree with swamp boogie, this calls for a related table as the development mess will only get worse and worse with this design.

 

[JefB]

Actually, I figured out what I was doing wrong, and now it works perfectly:

@ASrvd varchar should have been @ASrvd varchar(50) .

By not declaring the varchar size (in the variable), it was only using the first character, which means the pattern it was using was "c" and not "cle".

You may be right about efficiency, but I probably would only use this method on tables with 1000 or less records. In this particular case, the table is less than 100 records.

Thanks for the help.

JefB