I have the following stored procedure that I working on. I have noticed that every 5th or 6th time I refresh my results there are new values in there. Which considering that the data is in a static environment and no one is making any changes to the data at this time I really can't understand. Can someone please enlighten me as to why I would see different results even though I am running this procedure with the exact same parameters. I even tried it in query analyzer and still see the same strange results.
I am running in Sql 2008.
Here is the proc:
ALTER PROCEDURE [dbo].[SelectSearchBy_Category]
@userId INT,
@page INT,
@results INT,
@category NVARCHAR(50),
@searchTerm NVARCHAR(200) = NULL
AS
BEGIN
SET NOCOUNT ON
SET ROWCOUNT @results
DECLARE @categoryId INT
IF (@category IS NOT NULL) BEGIN
SET @categoryId = ( SELECT categoryId FROM Category WHERE categoryDescription = @category )
END
DECLARE @rowEnd INT
DECLARE @rowStart INT
SET @rowEnd = (@page * @results)
SET @rowStart = @rowEnd - @results
;WITH OrderedItems AS
(
SELECT
i.itemId,
title,
i.[description],
i.url,
i.categoryId,
i.ratingId,
i.requirements,
ISNULL(i.rating, 0) AS tating,
ISNULL(i.raters, 0) AS raters,
i.urlFriendlyPath,
ROW_NUMBER() OVER
(
ORDER BY i.dateAdded, (ISNULL(i.rating, 0) * ISNULL(i.raters, 0))
) AS RowNumber
FROM
[dbo].[Item] i
LEFT JOIN
UserItemIgnore uii ON uii.itemId = i.itemId AND uii.userId = @userId
INNER JOIN
ItemLanguage il ON il.itemId = i.itemId
WHERE
(@searchTerm IS NULL OR a.title LIKE '%' + @searchTerm + '%') AND
i.categoryId = @categoryId AND
il.languageId = 1 AND
uii.itemId IS NULL
)
SELECT *
FROM OrderedItems
WHERE RowNumber BETWEEN @rowStart AND @rowEnd
END