Spot the problem with this! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Spot the problem with this!

Author

Topic

Musician
Starting Member

6 Posts

Posted - 2002-03-22 : 10:11:15

Hello all,

I'm moving our staff telephone index from access to Sql Server 7. This is an index people use on our intranet where they can search for staff etc. I have written a procedure to search the first name (fname) and second name(sname) fields for the search text and it just returns nothing. So I'm hoping you guys might be able to tell me what's wrong with it:-

CREATE PROCEDURE up_GetContactsLikeNames
@stext varchar(100), @ctype int
AS
Select fname as [Name], sname, cGrades.grade as Grade, dsection as [Section], cDepartments.[name] as Department,
extensionno as Ext, email, pic, lcath, cath
from cContacts left join cGrades on cGrades.[id]=cContacts.gradeid left join cDepartments on cDepartments.[id]=cContacts.deptid
where ctypeid=@ctype and (fname like '%' + @stext + '%' or sname like '%' + @stext + '%')
order by sname, fname
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-22 : 10:22:26

Which table is the ctypeid column in? If it's on the right-hand side of a LEFT JOIN, but the ctypeid value you want is in a row that doesn't match the left-hand table, then you won't get any results.

Edited by - robvolk on 03/22/2002 10:22:51

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-22 : 10:22:35

This needs to be dynamic SQL; check out the articles:-

Part1:- http://www.sqlteam.com/item.asp?ItemID=4599
Part2:- http://www.sqlteam.com/item.asp?ItemID=4619

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-22 : 10:30:45

Sorry guys

Last post was entirely incorrect as someone has probably pointed out before I have posted this!

All I seem to do these days is dynamic SQL - oh - and deal with NULLs!

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

Musician
Starting Member

6 Posts

Posted - 2002-03-22 : 10:39:08

Thanks for the quick replies. ctypeid is in the cContacts table so the where part is only testing values from the one table, namely cContacts. I get no error message just no results so I'm wondering if the 2 left joins or nulls in the fields being searched might cause this to happen.

Musician
Starting Member

6 Posts

Posted - 2002-03-22 : 10:54:07

Arrrghhhh! I'm an idiot. Got it working - all content in the db is upper case and the like operator is obviously case sensitive. So I simply passed in @stext as the ucase of the search text and hey presto.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-22 : 11:06:11

Actually the like operator isn't case senstive unless your server is configured as case sensitive. And then EVERY comparison involving letters is case sensitive.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources