SitePoint Sponsor |
|
User Tag List
Results 1 to 1 of 1
-
Apr 23, 2004, 10:15 #1
- Join Date
- Feb 2002
- Location
- Colorado
- Posts
- 64
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Need help with SQL syntax for contains clause
I am trying to pass the firstname and lastname to a stored procedure in mssql 2000 and return all record related to the firstname lastname by using the full text search on a text column in a table. The problem I have is SQL doesnt like getting a phrase (with a space in it). For example, if I just use the lastname...or just use the firstname, it returns data. If I try to search where a column contains(column1, @name) it doesn't return data...and doesn't like the fact that there is a firstname (space) lastname in the @name variable. I must be missing something here.
I've also tried '@name'.
Here are my stored procedures, I have tried it both ways, first concatenating the fname lname into one variable and giving that the the SP, then tried firstname AND lastname.
CREATE PROCEDURE owner.procname
@name (50)
AS
SELECT HEADLINE, Date, PID, UID
FROM table
Where contains(column1, @name)
ORDER by UID DESC
GO
This errors out needing "'" between the fname and lname. If i use '@name' it returns no records.
Also I tried the above with
Where contains(complete, @firstname) AND contains(complete, @lastname)
and it doesn't find all records. It finds about 50% of records that have the matching data.
If anyone can help me, it is much appreciated. I'm sure it's some syntax error or somethin I'm not doing right.
EDIT: I have also just tried to query through enterprise manager with firstname lastname and it only returns 1 record. I know there's about 50-100 matching records, so I have no idea why the FT search isn"t working right.
I found any article on MS site that had issues with non SP3 versions and a space in a phrase. but this version is patched to SP3.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
ThanksLast edited by jlrosine; Apr 23, 2004 at 11:09.
Bookmarks