0

Would someone please help me.

I have a stored procedure (see below) for data paging in SQL Server 2005. The purpose of the stored procedure is to return a page of Products (prodid, description) for a particular Category (CatID).

When I execute the stored procedure:

EXEC @return_value = [dbo].[ecosys_CNET_GetCategoryProducts]  
@CatID = N'AA',  
@PageNumber = 1,  
@ProductsPerPage = 10,  
@HowManyProducts = @HowManyProducts OUTPUT

it only returns one (1) row even though there are many Products:

@HowManyProducts: 10034

I have a similar stored procedure which works just fine for smaller datasets. Am I hitting some sort of limit?

set ANSI_NULLS ON  
set QUOTED_IDENTIFIER ON  
go  

CREATE PROCEDURE [dbo].[ecosys_CNET_GetCategoryProducts]  

(  
@CatID char(2),  
@PageNumber INT,  
@ProductsPerPage INT,  
@HowManyProducts INT OUTPUT  
)  

AS  

-- Declare a new @Products TABLE variable.  
DECLARE @Products TABLE  
(  
RowNumber INT,  
prodid VARCHAR(40),  
description VARCHAR(2000)  
)  

-- Populate the @Product TABLE variable with the selected Products.  
INSERT INTO @Products  

SELECT  

ROW_NUMBER() OVER (ORDER BY cds_atr.prodid),  
cds_atr.prodid,  
cds_stdnee.description  

FROM cds_stdnee  

JOIN cds_atr  
ON (cds_stdnee.prodid = cds_atr.prodid)  

WHERE cds_atr.catid = @CatID  

-- Return the Total Number of Products using an OUTPUT variable.  
SELECT @HowManyProducts = COUNT(prodid) FROM @Products  

-- Extract the Requested Page of Products.  
SELECT DISTINCT prodid, description  

FROM @Products  

WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage  
  AND RowNumber <= @PageNumber * @ProductsPerPage
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Walter Lockhart
  • 1,293
  • 3
  • 20
  • 34

1 Answers1

1

If it's only returning one row, chances are there is only one row to return. Add a SELECT * FROM @Products and look to see what rows were there. In particular, look at the row numbers.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
  • Thanks John. I did what you suggested and discovered that the query return over 10 identical products. The DISTINCT was reducing that to 1 Product per page. I think I need to move the DISTINCT to the ROW_NUMBER clause to ensure a DISTINCT cds_atr.prodid. Can you advise how I do that please? – Walter Lockhart Mar 31 '09 at 19:26
  • I used GROUP BY instead of DISTINCT: INSERT INTO @Products SELECT ROW_NUMBER() OVER (ORDER BY cds_atr.prodid), cds_atr.prodid, cds_stdnee.description FROM cds_stdnee JOIN cds_atr ON (cds_stdnee.prodid = cds_atr.prodid) WHERE cds_atr.catid = @CatID GROUP BY cds_atr.prodid, cds_stdnee.description – Walter Lockhart Mar 31 '09 at 20:32