Is this the best way to do this ?

I have a query that has several optional fields to filtered the returned data from. They can either be null or have a value. This is the query that I have written. It works fine for filtered the data. But for some reason the query only ever returns 9 rows of data. No matter what parameter values I pass for @.startRowIndex and @.maximumRows.

Help.

SELECT CustomerName, Filename, UserName, DateAdded, PhotoID
FROM (SELECT CustomerName, Filename, UserName, DateAdded, PhotoID, ROW_NUMBER() OVER (ORDER BY Filename) AS RowNum
FROM (SELECT DISTINCT Photos.CustomerName, Photos.Filename, Photos.UserName, Photos.DateAdded, Photos.PhotoID
FROM Photos INNER JOIN
IndustryCatalog ON Photos.PhotoID = IndustryCatalog.PhotoID INNER JOIN
OptionCatalog ON Photos.PhotoID = OptionCatalog.PhotoID
WHERE (Photos.CustomerName LIKE '%' + @.CustomerName + '%' OR
@.CustomerName IS NULL) AND (Photos.UserName LIKE '%' + @.UserName + '%' OR
@.UserName IS NULL) AND (Photos.State LIKE '%' + @.State + '%' OR
@.State IS NULL) AND (Photos.City LIKE '%' + @.City + '%' OR
@.City IS NULL) AND (Photos.WorkOrderNumber = @.WorkOrder OR
@.WorkOrder IS NULL) AND (Photos.Series = @.Series OR
@.Series IS NULL) AND (Photos.ColorID = @.ColorID OR
@.ColorID IS NULL) AND (Photos.StructureWidth = @.StructureWidth OR
@.StructureWidth IS NULL) AND (Photos.StructureLength = @.StructureLength OR
@.StructureLength IS NULL) AND (IndustryCatalog.IndustryID = @.IndustryID OR
@.IndustryID IS NULL) AND (IndustryCatalog.AppID = @.AppID OR
@.AppID IS NULL) AND (OptionCatalog.CategoryID = @.CategoryID OR
@.CategoryID IS NULL) AND (OptionCatalog.OptionID = @.OptionID OR
@.OptionID IS NULL) AND (Photos.Country LIKE '%' + @.Country + '%' OR
@.Country IS NULL) AND (Photos.PhotoFinishNumber = @.PhotoFinishNumber OR
@.PhotoFinishNumber IS NULL) AND (Photos.Description LIKE '%' + @.Description + '%' OR
@.Description IS NULL) AND (Photos.Resolution > @.Resolution OR
@.Resolution IS NULL)) AS FilteredPhotos) AS Paged
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) - 1

Hi, I simplied you query and did a test in my database, the 2 parameters (@.startRowIndex and @.maximumRows) did work. There must be some other thing that caused the 2 parameters ineffective. Have you set the ROWCOUNT option? You can turn off the option by using this statement:

SET ROWCOUNT 0

|||Turns out I was using the wrong type of join in my query and thats what was screwing up my sql... Thanks for hte help though.