Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: using INFLECTIONAL in Stored Procedure

    Hi all
    I'm trying to add INFLECTIONAL searching in my Stored Procedure, but i'm not sure how to write it!
    I want my search to find all Inflections of my search key words.
    This only has to be applied to my Products.Product Field (FT_TBL.Product).
    Can anyone advise on how i add this to my stored procedure below please?
    Thanks
    Andy


    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[SPResults]
    @Parm1 as varchar(255),
    @Parm2 as int
    as
    
    SELECT ClientProducts.ClientID, FT_TBL.ProductID, FT_TBL.ManufacturerID, FT_TBL.Product, FT_TBL.Image, FT_TBL.Price, KEY_TBL.RANK, 
    COUNT(ClientOffers.ProductID) AS QtyOffers
    FROM dbo.Products
    AS FT_TBL 
    INNER JOIN dbo.ClientProducts 
    ON FT_TBL.ProductID = ClientProducts.ProductID
    LEFT OUTER JOIN 
    ClientOffers ON FT_TBL.ProductID = ClientOffers.ProductID 
    AND ClientOffers.ClientID = ClientProducts.ClientID 
    INNER JOIN CONTAINSTABLE(dbo.Products, *, 
    @Parm1, @Parm2) AS KEY_TBL
    ON FT_TBL.ProductID = KEY_TBL.[KEY]
    WHERE (ClientProducts.ClientID = 0)
    GROUP BY dbo.ClientProducts.ClientID, ClientOffers.ProductID, FT_TBL.ProductID, FT_TBL.Product, FT_TBL.Image, FT_TBL.Price, FT_TBL.ManufacturerID, KEY_TBL.RANK
    ORDER BY KEY_TBL.RANK DESC;

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    @Param1 should be something like:
    Code:
    containstable (Databases, comments, 'formsof (inflectional, "productname")', 20)
    Note that the function FORMSOF is entirely in single quotes, and the search term is in double quotes.

    What are you passing for @Param1?

  3. #3
    Join Date
    Feb 2008
    Posts
    120
    Hi MCrowley

    Thanks for replying

    I'm passing multiple keywords to @param1 using an array like:

    Results_Comm__Parm1 = Results_Comm__Parm1 & " AND " & chr(34) & chr(42) & sSearchArray(i) & chr(42) & chr(34)

    I'm guessing the code you supplied would be changed to sometinhg like this in my instance

    containstable (mydatabase, products, 'formsof (inflectional, "product")', 20)

    Still not sure where this would go though!

    I use this in MS SQL Management Studio:
    Code:
    GO 
    DECLARE @Parm1 varchar(255)
    DECLARE @Parm2 int
    
    SET @Parm1 = '"ideal" AND "multimeter"'
    SET @Parm2 = 300
    
    SELECT ClientProducts.ClientID, FT_TBL.ProductID, FT_TBL.ManufacturerID, FT_TBL.Product, FT_TBL.Image, FT_TBL.Price, KEY_TBL.RANK, 
    COUNT(ClientOffers.ProductID) AS QtyOffers
    FROM dbo.Products
    AS FT_TBL 
    INNER JOIN dbo.ClientProducts 
    ON FT_TBL.ProductID = ClientProducts.ProductID
    LEFT OUTER JOIN 
    ClientOffers ON FT_TBL.ProductID = ClientOffers.ProductID 
    AND ClientOffers.ClientID = ClientProducts.ClientID 
    INNER JOIN CONTAINSTABLE(dbo.Products, *, 
    @Parm1, @Parm2) AS KEY_TBL
    ON FT_TBL.ProductID = KEY_TBL.[KEY]
    WHERE (ClientProducts.ClientID = 0)
    GROUP BY dbo.ClientProducts.ClientID, ClientOffers.ProductID, FT_TBL.ProductID, FT_TBL.Product, FT_TBL.Image, FT_TBL.Price, FT_TBL.ManufacturerID, KEY_TBL.RANK
    ORDER BY KEY_TBL.RANK DESC;
    GO
    For the above, i need the word multimeter, to also find multimeters.

    Thank You
    Andy

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    To have multiple search terms, you would need to wrap each in a FORMSOF function. Try this:
    Code:
    SET @Parm1 = 'formsof (inflectional, "ideal") AND formsof (inflectional, "multimeter")'

  5. #5
    Join Date
    Feb 2008
    Posts
    120
    Thanks again MCrowley
    This works perfectly in MS SQL Query Analyzer, where the values are hard coded in, but how should i write it using the variables?
    Should this be written in my asp page or in the stored procedure?

    Many Thanks
    Andy

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You would probably want to build up the string in the application layer, and then just pass that as a variable to the stored procedure. You have a base pattern {formsof (inflectional, "?")}, and all you are doing is replacing the "?" with the keywords, and sticking "AND"s between them all.

Posting Permissions

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