using Like Opertor in Stored Procedure

si_the_Geek · April 10th, 2007, 11:18 AM

How i use a Like Operator in a parameter based SQL stored Procedure

the procedure is as follows

create PROCEDURE emplike
@fname varchar
AS
Declare @sql as varchar(2000)

    if (@fname='')
        SELECT * FROM employee
    else
        SELECT * FROM employee
        WHERE fname like '%' + @fname + '%'

happygv · April 10th, 2007, 11:29 AM

This should work for a basic like search, I did a bit of changes down there. BTW, are you facing any issues with your current code?

Code:

create PROCEDURE emplike
@fname varchar=NULL
AS
Declare @sql as varchar(2000)

    if (@fname IS NULL)
        SELECT * FROM employee        
    else
        SELECT * FROM employee
        WHERE  fname like '%' + @fname + '%'

_________________________
- Vijay G
Strive for Perfection

si_the_Geek · April 10th, 2007, 11:36 AM

i'm using the pubs database and Employee table for this purpose...
U can test this .
In query analyser when i execute this command
exec emplike 'p'
i get all the records whose first name starts with p..
till here no issues
but when i execute this command in query analyser
exec emplike 'pa'
i still get the same results i.e all the records whose first name starts with p....it is not filtered for pa.

please suggest.

dparsons · April 10th, 2007, 11:44 AM

Well Executing the statement:

SELECT * FROM employee
WHERE fname like '%' + @fname + '%'

is increasingly different then executing
SELECT * FROM employee
WHERE fname like @fname + '%'

In the first example, SQL Searches the fname field for ANY instance of, in your case, pa. So, if there was a name in the database of SPADE that would be returned by this query because the string SPADE does, in fact, contain the string 'PA'.

If you are looking for ONLY strings that begin with a letter and not necessarily contained anywhere in the string, you would use the second example above and it will only return names that begin with PA.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

happygv · April 10th, 2007, 11:51 AM

Quote:

quote:Originally posted by si_the_Geek
U can test this .
In query analyser when i execute this command
exec emplike 'p'
i get all the records whose first name starts with p..
till here no issues

I am sure this wouldn't have JUST returned all the records whose first name starts with P with the code that you posted in your original post, due to the PREFIXED '%' there. You should be removing it to have it work the way you wanted.

Cheers.

_________________________
- Vijay G
Strive for Perfection

sshelper · April 12th, 2007, 09:38 AM

You have to specify the length of the VARCHAR in your parameter. Since you didn't specify it, it defaults to 1. This is the reason why whether you enter 'P' or 'PA', both translates to 'P' because of the default length.

create PROCEDURE emplike
@fname varchar(10)

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

happygv · April 12th, 2007, 09:51 AM

Oh!... Too many things missing in there. I never looked at that. But still it should have returned all those rows having P somewhere in the FName.

_________________________
- Vijay G
Strive for Perfection