SQL Server Performance

Problem with Stored Procedure

Discussion in 'SQL Server 2005 General Developer Questions' started by Mr.Mando, Jun 30, 2009.

  1. Mr.Mando New Member

    Hello,I have a problem with the stored procedureI'm using Full-Text Catalog to search the tables
    Here is my SP

    =================================================
    ALTER PROCEDURE [dbo].[SearchBooks]
    @ItemName
    nvarchar(50),@Keywords
    nvarchar(50)AS
    set
    @ItemName = '%' + @ItemName + '%'set
    @Keywords = '%' + @Keywords + '%'SELECT[FileID]
    ,[UserID]
    ,[BookName]
    ,[AdditionDateTime]
    ,[FilePath]
    ,[Author]
    ,[Views]
    ,[Comment]
    ,[CoverPicture]
    ,[DeptID]
    ,[UserIP]FROM[Books]WHERE[BookName]
    like @ItemNameORCONTAINS
    (Keywords, @Keywords)
    =================================================
    The problem happens when I want to search for any text contains a space such as 'My Book' I get the following error
    Syntax error near 'My' in the full-text search condition '% My Book %'
    Please tell me what is the reason of this error and How can I fix it ?
    Thanks
  2. atulmar New Member

    I haven't used FTS, however looks like FTS doesn't need wild card in search terms.
  3. Sandy New Member

    HI...,
    Before giving you answer I need one clarification from your end that why are you using both type of search? I mean to say you can use Like Or Contains then why Both? what is your datatype for Keyword column?
    When you are using full Text Index in the contains you need to use the '*' instead of '%' for the search criteria.
    You can use this @Keywords = @Keywords + '*' instead of @Keywords = '%' + @Keywords + '%'
    Exm: @Keyword = '"My Books*"' -- IMP: you need to enclose the keyword with double quotes
    Note: it will only search which start with @keyword.
    Thank you,
    Sandy.
  4. Mr.Mando New Member

    Hi Sandy,
    Thank you for your reply
    I'm still beginner with SQL Server and I used them for training
    I had read it in some lessons about FTS I can use both like and Contains to search the tables but in fact I don't understand what the Contains keyword functionality (May be its like the keyword 'like')
    the Keyword column is nvarchar
    using @Keywords = @Keywords + '*' is good
    But what if I want to search for any string contains the @Keyword ?
    for Example: @Keyword = 'm'
    and there are some records like
    Mando
    Supreme
    Exam
    How can I get them all ?
    Thanks
  5. Sandy New Member

    Thanks,
    1st Answer: See, one thing I can say you that the use of Like and Full Text Index search are used for different reasons. If the column type is varchar/nvarchar type then we can go with Like Keyword search where data are limited but just imagine where data size is very large like text/image then its very hard to search by using like keyword. So Full Text Search is used. In your case you can use Like instead of CONTAINS.
    2nd: In full text search you can only search a pattern which starts with something just like "S*" it wil search the words starts with "S".
    Please check this: http://msdn.microsoft.com/en-us/library/ms142571.aspx
    Note: You can use Like keyword for the 2nd option
    Hope you are clear now.
    Thank you,
    Sandy.
  6. Mr.Mando New Member

    Thanks Sandy,
    I tried using 'like' keyword but I got no results!
    I have some records contains
    Csharp Book
    Csharp Book
    and I tried to search for 'Csharp B' but I got no results
    I used
    set @ItemName = '"' + 'Csharp B' + '*"'
    set @Keyword = '"' + 'Csharp B' + '*"'
    ------------------------------------------------
    The problem happens only when I use 'CONTAINS' keyword but if I used 'LIKE' keyword it works without any problem even with '%'
    Bu as you said 'CONTAINS' is useful with the large columns, I already have some columns in other tables contains a very large data length
    How do I use contains with it ?
    Thanks
  7. Adriaan New Member

    CONTAINS can only be used if the table has a "full-text index".
    If it has only regular indexes, use LIKE.
  8. Mr.Mando New Member

    Thanks Adriaan for your reply
    I created a Full-Text Catalog and assigned the tables to it
  9. Adriaan New Member

    Also, it looks like the double quotes are required with the CONTAINS syntax.
    If you have a double quote in a string that you use with LIKE, then the double quote is interpreted as part of the string that you're searching for. (Unless you're using specific ANSI settings.)
  10. Mr.Mando New Member

    Thank you adriaan
    That's it, it works fine now with the double qoutesset @Keywords = '"%' + 'Csharp b' + '%"'
    Now I can use CONTAINS and LIKE
    as I understood from Sandy it's better to use CONTAINS with the large fields and with FTS
    is there any other tips for using LIKE and CONTAINS ?
    Thanks
  11. Sandy New Member

    [quote user="Adriaan"]
    Also, it looks like the double quotes are required with the CONTAINS syntax.
    If you have a double quote in a string that you use with LIKE, then the double quote is interpreted as part of the string that you're searching for. (Unless you're using specific ANSI settings.)
    [/quote]
    Thanks Adriaan,
    Thank you,
    Sandy.

Share This Page