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
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.