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?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,|||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 (http://search.msn.com/developer).

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