Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    4

    Unanswered: Full-Text Search and Output Parameters

    Hi,

    I'd like to incorporate search functionality (SQL Server 2005 Full-Text Search) into a web application, so I want to be able to return a paged list of results based on the user's search terms. I already have a parameterized stored procedure that returns a list of products when a category ID is supplied. I modified this procedure to use a different input parameter (@SearchTerms), but I'd still like to return the number of records, as in the original stored procedure.

    However, I'm getting this error: Invalid object name 'ProductEntries'.

    Here's the original stored procedure:

    ALTER PROCEDURE dbo.GetProductsByCategoryID
    (
    @CategoryID INT,
    @PageIndex INT,
    @NumRows INT,
    @CategoryName VARCHAR(50) OUTPUT,
    @CategoryProductCount INT OUTPUT
    )
    AS

    BEGIN
    SELECT @CategoryProductCount = (SELECT COUNT(ProductID)
    FROM Products
    WHERE Products.CategoryID = @CategoryID)
    SELECT @CategoryName = (SELECT CategoryName
    FROM Categories
    WHERE Categories.CategoryID = @CategoryID)

    DECLARE @startRowIndex INT;
    SET @startRowIndex = (@PageIndex * @NumRows) + 1;

    WITH ProductEntries AS (
    SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS Row, ProductID, CategoryID, Description, ProductImage, UnitCost
    FROM Products
    WHERE CategoryID = @CategoryID
    )

    SELECT ProductID, CategoryID, Description, ProductImage, UnitCost
    FROM ProductEntries
    WHERE Row BETWEEN
    @startRowIndex AND @startRowIndex + @NumRows - 1

    END


    And here's the modified one:

    ALTER PROCEDURE dbo.GetSearchResults
    (
    @SearchTerms VARCHAR(200),
    @PageIndex INT,
    @NumRows INT,
    @ProductCount INT OUTPUT
    )
    AS

    BEGIN
    SELECT @ProductCount = (SELECT COUNT(ProductID)
    FROM ProductEntries)

    DECLARE @startRowIndex INT;
    SET @startRowIndex = (@PageIndex * @NumRows) + 1;

    WITH ProductEntries AS (
    SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS Row, ProductID, CategoryID, Description, ProductImage, UnitCost
    FROM CONTAINSTABLE (Products, *, @SearchTerms, 25) AS c, Products p
    WHERE c.[KEY] = p.ProductID
    )

    SELECT ProductID, CategoryID, Description, ProductImage, UnitCost
    FROM ProductEntries
    WHERE Row BETWEEN
    @startRowIndex AND @startRowIndex + @NumRows - 1

    END

    I thought I might be getting this error because SELECT @ProductCount occurs before the ProductEntries table is created, but when I move that SELECT statement further down, I still get the same error.

    How can I get the value of @ProductCount in this scenario so that I can display it in the UI of the web app?

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The first to do would be to directly query the ProductEntries table from both a Stored Procedure and then as an individual statement. In 99% of cases, this solves the problem.

    A possible and common reason for your error may have to do with ownership properties of the table and their association, or lack thereof, to the account that you are using to create the stored procedure.

    I haven't used SQL Server for a few months now, as I've just recently started working on an Oracle project, and I cannot recall exactly the relationship between CREATE and EXECUTE on stored procedures and which of the above explicitly allows access to the underlying objects that will be used by the stored procedure.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Nov 2007
    Posts
    4
    Hi Robert,

    Thanks for your suggestions. Unfortunately, I could not get the procedure to work. After further research, I decided not to rely on SQL Server Full-Text Search for my site's search engine because it's really not practical in a shared hosting environment. So, I think I'll put this one to rest and check out the MSN Search SDK.

    Anyway, thanks again for your reply -- it was much appreciated.

Posting Permissions

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