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.

Is this SQL stored prodcedure is valid

what i want to achive is the proc sh'd return a master-detail value in one go.
master value should be returned with Out Parameter and detail value as a recordset.

will it return the recordset of detail table as below...

Create Procedure ProductDetail
(
 @.ProductID int,
 @.ProductCode varchar(15) OUTPUT,
 @.ProductName varchar(60) OUTPUT,
 @.CategoryID int OUTPUT,
 @.CategoryName varchar(60) OUTPUT,
 @.Image1 varchar(256) OUTPUT,
 @.Image2 varchar(256) OUTPUT,
 @.UnitPrice smallmoney OUTPUT,
 @.UOMValue numeric(9) OUTPUT,
 @.UOMName varchar(10) OUTPUT,
 @.ShippingWeight numeric(9) OUTPUT,
 @.Directions varchar(1500) OUTPUT,
 @.Ingrediants varchar(1500) OUTPUT,
 @.Warnings varchar(1500) OUTPUT,
 @.ShortDescription varchar(1000) OUTPUT,
 @.LongDescription varchar(2000) OUTPUT,
 @.NutritionFacts varchar(1000) OUTPUT,
 @.SearchKeywords varchar(500) OUTPUT,
 @.IsTaxable varchar(15) OUTPUT,
 @.CreatedBy varchar(60) OUTPUT,
 @.CreatedOn varchar(15) OUTPUT,
 @.UpdatedBy varchar(60) OUTPUT,
 @.UpdatedOn varchar(15) OUTPUT,
 @.Status int OUTPUT
)
AS
SELECT
 @.ProductCode = ProductCode,
 @.ProductName = ProductName,
 @.CategoryID = CategoryID,
 @.CategoryName = (select CategoryName from mCategory where CategoryID=a.CategoryID),
 @.Image1 = isnull(Image1,''),
 @.Image2 = isnull(Image1,''),
 @.UnitPrice = isnull(UnitPrice,0),
 @.UOMValue = isnull(UOMValue,0),
 @.UOMName = isnull(UOMName,''),
 @.ShippingWeight = isnull(ShippingWeight,0),
 @.Directions = isnull(Directions,''),
 @.Ingrediants = isnull(Ingrediants,''),
 @.Warnings = isnull(Warnings,''),
 @.ShortDescription = isnull(ShortDesc,''),
 @.LongDescription = isnull(LongDesc,''),
 @.NutritionFacts = isnull(NutritionFacts,''),
 @.SearchKeywords = isnull(SearchKeywords,''),
 @.IsTaxable = case when isnull(IsTaxable,0)=0 then 'No' else 'Yes' End,
 @.CreatedBy = isnull((select LName + ',' + FName from mUser where UserID=InsertedBy),''),
 @.CreatedOn = InsertedOn,
 @.UpdatedBy = isnull((select LName + ',' + FName from mUser where UserID=UpdatedBy),''),
 @.UpdatedOn = UpdatedOn,
 @.Status = Convert(int,isnull(Status,0))
FROM
 mProduct a
WHERE
 ProductID = @.ProductID
SELECT
 ID as PricingDetailID,
 isnull(PricingFromQnty,0) as PricingFromQnty,
 isnull(PricingToQnty,0) as PricingToQnty,
 isnull(RangePrice,0) as RangePrice,
 Convert(int,isnull(Status,0))as Status
FROM
 dProduct
WHERE
 ProductID = @.CategoryID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Regards,
BhairavI believe the way you are doing it is possible, but why not return two recordsets back to a dataset? Then you would have a master datatable and detail datatable. I believe this would work:

Create Procedure ProductDetail
(
 @.ProductID int
)
AS
SELECT
 ProductCode,
 ProductName,
 CategoryID,
 (select CategoryName from mCategory where CategoryID=a.CategoryID),
 isnull(Image1,''),
 isnull(Image1,''),
 isnull(UnitPrice,0),
...
FROM
 mProduct a
WHERE
 ProductID = @.ProductID
SELECT
 ID as PricingDetailID,
 isnull(PricingFromQnty,0) as PricingFromQnty,
 isnull(PricingToQnty,0) as PricingToQnty,
 isnull(RangePrice,0) as RangePrice,
 Convert(int,isnull(Status,0))as Status
FROM
 dProduct
WHERE
 ProductID = @.CategoryID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

HTH|||thks for u'r suggesion
but can u plz explain me in more detail ...
how my dataset code will look like when a single strore proc return more than one recordset.

proc must not be called more than once for that...

Regards,
Bhairav|||Sure,
Just follow the code (I'm using Microsoft Data Access Application Blocks to call):

SqlParameter [] arParms = new SqlParameter[1];
arParms[0] = new SqlParameter("@.ProductID", SqlDbType.Int);
arParms[0].Value = 1;
DataSet myDS = SQLHelper.ExecuteDataset("connectiion", StoredProcedure, "ProductDetail", arParms);
DataTable myTable1 = myDS.Tables[0];
DataTable myTable2 = myDS.Tables[1];

That should give you an example of the calling code. There are other ways to manipulate the dataset data. If you are unfamiliar, just hollar and we can give you some direction, or search the archives of the data access forms. HTH|||thks..
its really the nice way to code
thks again

Regards,
Bhairav