Using LIKE in a stored procedure wtih input parameters

[Guest_imported]

We are creating a stored procedure with a select statement and using input parameters. We use this code as follows, the top line of the SP says: Create Procedure SkillSP @Skill varchar(30) and then the WHERE clause states: WHERE SK1Name = @Skill. We would like to take out the = and use a LIKE statement so that we aren't limited to retrieving just exact hits. What is the proper format for using LIKE with an input parameter in a stored procedure that contains a SELECT statement?

 

[fluteplr]

Create Procedure SkillSP @Skill varchar(30)
as
set @skill = @skill + '%'

put select code here

WHERE SK1Name = @Skill

You can put the % on both sides to loke for the string anywhere in the field, but the performance will probably not be good since the server will no longer be able to use an index.

 

[rac2]

Hello,

On MS SQL Server here are two approaches that work. (I don't know how proper they might be ;-).)

First approach assumes that the pattern to match is defined in the stored procedure.

CREATE PROCEDURE [proc_get_recruits_like] (
	@Skill VARCHAR(30)
)
AS

DECLARE @SimilarSkill VARCHAR(32)

SET @SimilarSkill = '%' + @Skill + '%'

SELECT * FROM recruits
WHERE SK1Name LIKE  @SimilarSkill

The second approach assumes that the parameter contains the pattern of wildcard characters you want to match. For example, '%skat%'.

CREATE PROCEDURE [proc_get_recruits_like] (
	@Skill VARCHAR(30)
)
AS

SELECT * FROM recruits
WHERE SK1Name LIKE  @Skill

Either approach will match 'skat' with skating, figure-skating, speed-skater, etc. Other combinations of wildcards could be predefined in the stored procedure or generated in your application and passed to the stored procedure. But this kind of free-form searching is inherently incomplete.

It is better to pre-define the values that a column may contain and use a structured form that controls the values entered. That way you know what to search for when retrieving the data.

 

[r937]

You can put the % on both sides ... but the performance will probably not be good since the server will no longer be able to use an index.

apologies if this follow-up question isn't exactly ansi sql, but is that really true? if the index contains all values of a column, and you're searching for hits within that column, why wouldn't the database search the index (i.e. do an index scan) before retrieving records? anything else doesn't make sense to me -- but then, i freely admit i'm not a dba

rudy

http://rudy.ca/

 

[tlbroadbent]

If you place widcards on the left side of the search string, MS SQL Server will not use the Index. Why not? Because you've asked to search for the string anywhere in the column. SQL must scan the table or index to find the data.

With a wildcard only on the right side of the string, SQL can still utilize the index because it will search on the left N characters of the indexed column and the the Left N characters will be ordered properly. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[karluk]

To clarify, the reason that an index is ususally not used in a "where char_data like '%abc%'" query is because of performance, not because it's impossible to use the index.

The problem is that the string 'abc' can occur anywhere in the index, from 'abcd' to 'zabc'. The database optimizer knows this, so it knows that it will have to scan through the entire index looking for matches. In addition it will have to do extra disk reads to retrieve matching rows from the table.

In comparison, bypassing the index forces a full scan of the table data. In general the optimizer will predict that a full index scan plus extra table reads will be more costly than a full table scan. In individual cases this may not be true, which is where performance tuning comes in. Most databases allow one to give optimizer hints that direct it to use an index that might otherwise be bypassed.

 

[r937]

In general the optimizer will predict that a full index scan plus extra table reads will be more costly than a full table scan.

thanks, that was the part i was having trouble with

obviously there are going to be cases where for example an indexed char(20) field is going to be a lot faster to scan than a table with multiple VARCHAR(4000)'s in it -- *way* more I/O to scan the table, neh?

i knew about HINT in oracle but i didn't realize it would be so widely necessary...

rudy

 

[fluteplr]

I guess I should have been more specific in my original statement. But you all seem to have clarified it nicely.