SitePoint Sponsor

User Tag List

Results 1 to 1 of 1
  1. #1
    SitePoint Enthusiast jlrosine's Avatar
    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)



    Thanks
    Last edited by jlrosine; Apr 23, 2004 at 10:09.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •