Results 1 to 1 of 1
Apr 23, 2004, 09:15 #1
- Join Date
- Feb 2002
- 0 Post(s)
- 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
SELECT HEADLINE, Date, PID, UID
Where contains(column1, @name)
ORDER by UID DESC
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)
Last edited by jlrosine; Apr 23, 2004 at 10:09.