Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 10th, 2007, 11:18 AM
Registered User
 
Join Date: Apr 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default using Like Opertor in Stored Procedure

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 + '%'



 
Old April 10th, 2007, 11:29 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old April 10th, 2007, 11:36 AM
Registered User
 
Join Date: Apr 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.



 
Old April 10th, 2007, 11:44 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old April 10th, 2007, 11:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old April 12th, 2007, 09:38 AM
Authorized User
 
Join Date: Oct 2005
Posts: 72
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old April 12th, 2007, 09:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Procedure jezywrap SQL Server ASP 1 January 3rd, 2007 12:29 AM
stored procedure kdm260 SQL Server 2000 2 June 19th, 2006 04:45 PM
Help On Stored Procedure desireemm SQL Language 2 October 31st, 2005 07:11 PM
Stored Procedure rajanikrishna SQL Server 2000 0 July 18th, 2005 05:01 AM
Help About Stored Procedure zhuge6 BOOK: ASP.NET Website Programming Problem-Design-Solution 3 May 20th, 2005 09:27 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.