help with implementing paging in sql sever 2000

• Tom Bathgate

  SSC Journeyman

  Points: 82

  More actions

  November 29, 2006 at 5:53 am

  #115191

  HI All,

  I was hoping someone would be able to help me with some thing. I am learning ASP.NEt v 2.0 and am using SQL server 2000 developerd edition to build an exommerce website using the Apress booking Beginning E-Commerce in C# 2005.

  I have been managing fine up to now but the book is building stored procedures for retrieving products lists from the database. The book is written for SQL 2005 express edition but says that with some extra woork there is no problems with getting the code to work in 2000. I have two procedures that implement paging which the book says doesn't work in sql 2000 and gives a very brief solution which doesn't make sense to me. Would anybody be able to help me convert these so i can use them in sql 2000?

  I get the rough idea but am having problems re-writing this and I am now holding my hands and admitting defeat.

  The procedures are as follows:

  CREATE PROCEDURE GetProductsOnDepartmentPromotion

  (@DepartmentID INT,

  @DescriptionLength INT,

  @PageNumber INT,

  @ProductsPerPage INT,

  @HowManyProducts INT OUTPUT)

  AS

  -- declare a new TABLE variable

  DECLARE @Products TABLE

  (RowNumber INT,

  ProductID INT,

  Name VARCHAR(50),

  Description VARCHAR(5000),

  Price MONEY,

  Image1FileName VARCHAR(50),

  Image2FileName VARCHAR(50),

  OnDepartmentPromotion BIT,

  OnCatalogPromotion BIT)

  -- populate the table variable with the complete list of products

  INSERT INTO @Products

  SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS Row,

  ProductID, Name, SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,

  Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion

  FROM

  (SELECT DISTINCT Product.ProductID, Product.Name,

  SUBSTRING(Product.Description, 1, @DescriptionLength) + '...' AS Description,

  Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion

  FROM Product INNER JOIN ProductCategory

  ON Product.ProductID = ProductCategory.ProductID

  INNER JOIN Category

  ON ProductCategory.CategoryID = Category.CategoryID

  WHERE Product.OnDepartmentPromotion = 1

  AND Category.DepartmentID = @DepartmentID

  ) AS ProductOnDepPr

  -- return the total number of products using an OUTPUT variable

  SELECT @HowManyProducts = COUNT(ProductID) FROM @Products

  -- extract the requested page of products

  SELECT ProductID, Name, Description, Price, Image1FileName,

  Image2FileName, OnDepartmentPromotion, OnCatalogPromotion

  FROM @Products

  WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage

  AND RowNumber <= @PageNumber * @ProductsPerPage GO And: CREATE PROCEDURE GetProductsOnCatalogPromotion
  (@DescriptionLength INT,
  @PageNumber INT,
  @ProductsPerPage INT,
  @HowManyProducts INT OUTPUT)
  AS

  -- declare a new TABLE variable
  DECLARE @Products TABLE
  (RowNumber INT,
  ProductID INT,
  Name VARCHAR(50),
  Description VARCHAR(5000),
  Price MONEY,
  Image1FileName VARCHAR(50),
  Image2FileName VARCHAR(50),
  OnDepartmentPromotion BIT,
  OnCatalogPromotion BIT)

  -- populate the table variable with the complete list of products
  INSERT INTO @Products
  SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
  ProductID, Name,
  SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,
  Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
  FROM Product
  WHERE OnCatalogPromotion = 1

  -- return the total number of products using an OUTPUT variable
  SELECT @HowManyProducts = COUNT(ProductID) FROM @Products

  -- extract the requested page of products
  SELECT ProductID, Name, Description, Price, Image1FileName,
  Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
  FROM @Products
  WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage

  AND RowNumber <= @PageNumber * @ProductsPerPage GO And: CREATE PROCEDURE GetProductsInCategory
  (@CategoryID INT,
  @DescriptionLength INT,
  @PageNumber INT,
  @ProductsPerPage INT,
  @HowManyProducts INT OUTPUT)
  AS

  -- declare a new TABLE variable
  DECLARE @Products TABLE
  (RowNumber INT,
  ProductID INT,
  Name VARCHAR(50),
  Description VARCHAR(5000),
  Price MONEY,
  Image1FileName VARCHAR(50),
  Image2FileName VARCHAR(50),
  OnDepartmentPromotion BIT,
  OnCatalogPromotion BIT)

  -- populate the table variable with the complete list of products
  INSERT INTO @Products
  SELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),
  Product.ProductID, Name,
  SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,
  Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
  FROM Product INNER JOIN ProductCategory
  ON Product.ProductID = ProductCategory.ProductID
  WHERE ProductCategory.CategoryID = @CategoryID

  -- return the total number of products using an OUTPUT variable
  SELECT @HowManyProducts = COUNT(ProductID) FROM @Products

  -- extract the requested page of products
  SELECT ProductID, Name, Description, Price, Image1FileName,
  Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
  FROM @Products
  WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage

  AND RowNumber <= @PageNumber * @ProductsPerPage GO I know these are long but from what the book claims is that they dont take much to re-write and only really need tweaking. i would be grateful for any help or anyone that can point me in the right direction with this. Thanks in advance Tom

• Jules Bonnot

  SSCertifiable

  Points: 5488

  More actions

  November 29, 2006 at 7:21 am

  #674640

  Row_namber() is 2005 only and it is this that generates the id column which is later filterd on.

  in 2000 i would the intial record set into a table variable with an identity column and then select the data you want filter on that column.

  declare

  @start int, @end int, @size int,@page int

  set @page = 2 -- which page of data to return

  set

  @size = 20 -- 20 rows per page

  set

  @start = (((@page - 1) * @size) + 1)

  set

  @end = (@start + @size - 1)

   

  declare

  @tab table (id int identity, ColumnValue varchar(1000))-- table to hold results of your query

  insert

  @tab

  select

  name--- your query here

  from

  syscolumns

  select

  *

  from

  @tab

  where

  id >= @start and id <= @end -- filter results for required page.

  www.sql-library.com[/url]

• Tom Bathgate

  SSC Journeyman

  Points: 82

  More actions

  November 29, 2006 at 7:29 am

  #674645

  Hi,

  Thanks for the reply so what you woul do is declare the table variable, and then create the table, and then you could continue with the code that comes after this?

  Tom

• Jules Bonnot

  SSCertifiable

  Points: 5488

  More actions

  November 29, 2006 at 7:35 am

  #674647

  post the select statement that returns the record set you want paged into chunks.

  www.sql-library.com[/url]

• Tom Bathgate

  SSC Journeyman

  Points: 82

  More actions

  November 29, 2006 at 7:41 am

  #674652

  This is one of them:

  -- populate the table variable with the complete list of products

  INSERT INTO @Products

  SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS Row,

  ProductID, Name, SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,

  Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion

  FROM

  (SELECT DISTINCT Product.ProductID, Product.Name,

  SUBSTRING(Product.Description, 1, @DescriptionLength) + '...' AS Description,

  Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion

  FROM Product INNER JOIN ProductCategory

  ON Product.ProductID = ProductCategory.ProductID

  INNER JOIN Category

  ON ProductCategory.CategoryID = Category.CategoryID

  WHERE Product.OnDepartmentPromotion = 1

  AND Category.DepartmentID = @DepartmentID

  ) AS ProductOnDepPr

  -- return the total number of products using an OUTPUT variable

  SELECT @HowManyProducts = COUNT(ProductID) FROM @Products

  -- extract the requested page of products

  SELECT ProductID, Name, Description, Price, Image1FileName,

  Image2FileName, OnDepartmentPromotion, OnCatalogPromotion

  FROM @Products

  WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage

  AND RowNumber <= @PageNumber * @ProductsPerPage

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738612

  More actions

  November 29, 2006 at 7:46 am

  #674654

  I think you can continue with it. At first glance I don't see any other 2005 features in there.

• Tom Bathgate

  SSC Journeyman

  Points: 82

  More actions

  November 29, 2006 at 7:47 am

  #674655

  Ok I'll try it and see how i get on. But i may be back for some help 😉

  Thanks

  Tom

• Jules Bonnot

  SSCertifiable

  Points: 5488

  More actions

  November 29, 2006 at 7:49 am

  #674656

  --try this. note that the table variable defintion must fit the select statement, I have guessed at  some of the data types.

   

  declare

  @start int, @end int, @size int,@page int

  set

  @page = 2 -- which page of data to return

  set

  @size = 20 -- 20 rows per page

  set

  @start = (((@page - 1) * @size) + 1)

  set

  @end = (@start + @size - 1)

   

  declare

  @Products table (id int identity, ProductID int, name varchar(1000), Description varchar(1000),

  price

  decimal (18,6), Image1FileName varchar(1000), OnDepartmentPromotion varchar(1000), OnCatalogPromotion varchar(1000))

  INSERT

  INTO @Products

  SELECT

  ProductID

  , Name, SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,

  Price

  , Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion

  FROM

  (

  SELECT DISTINCT Product.ProductID, Product.Name,

  SUBSTRING

  (Product.Description, 1, @DescriptionLength) + '...' AS Description,

  Price

  , Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion

  FROM

  Product INNER JOIN ProductCategory

  ON

  Product.ProductID = ProductCategory.ProductID

  INNER

  JOIN Category

  ON

  ProductCategory.CategoryID = Category.CategoryID

  WHERE

  Product.OnDepartmentPromotion = 1

  AND

  Category.DepartmentID = @DepartmentID

  )

  AS ProductOnDepPr

  -- return the total number of products using an OUTPUT variable

  SELECT

  @HowManyProducts = scope_identity()

  -- extract the requested page of products

  SELECT

  ProductID, Name, Description, Price, Image1FileName,

  Image2FileName

  , OnDepartmentPromotion, OnCatalogPromotion

  FROM

  @Products

  where

  id >= @start and id <= @end

  www.sql-library.com[/url]

• Tom Bathgate

  SSC Journeyman

  Points: 82

  More actions

  November 29, 2006 at 9:42 am

  #674709

  HI,

  thanks for the code, i still cant this to work i have all sorts of errors popping up, I just cant seem to get this to work. I'm popping out but i'll post the code i was trying later to see what i'm doing wrong.

  thanks

  Tom

• Tom Bathgate

  SSC Journeyman

  Points: 82

  More actions

  November 29, 2006 at 1:12 pm

  #674790

  Hi All,

   

  I have been playing with this code and have finally got something that is accepted without any errors. Whats supposed to happen is that when you click on a department a list of products is supposed to appear based on those that are on promotion.

  When you click on the category this is supposed to refine the list of products further and offer a means to page between each page of products.

  However nothing is being returned now. Can anyone tell me what wrong with this?

  ^{ALTER PROCEDURE}

  ^{GetProductsOnDepartmentPromotion}

  ^{(@DepartmentID}

  ^INT,

  ^{@DescriptionLength}

  ^INT,

  ^@PageNumber

  ^INT,

  ^{@ProductsPerPage}

  ^INT,

  ^{@HowManyProducts}

  ^{INT OUTPUT)}

  ^AS

  ^{-- declare a new TABLE variable}

  ^DECLARE

  ^{@Products TABLE}

  ^(RowNumber

  ^{SMALLINT IDENTITY (1,1) NOT NULL,}

  ^ProductID

  ^INT,

  ^{Name VARCHAR(50),}

  ^Description

  ^{VARCHAR(5000),}

  ^Price

  ^MONEY,

  ^{Image1FileName}

  ^VARCHAR(50),

  ^{Image2FileName}

  ^VARCHAR(50),

  ^{OnDepartmentPromotion}

  ^BIT,

  ^{OnCatalogPromotion}

  ^BIT)

  ^{-- populate the table variable with the complete list of products}

  ^{INSERT INTO}

  ^@Products

  ^SELECT

  ^{ProductID, Name, SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price, Image1FileName, Image2FileName,}

  ^{OnDepartmentPromotion, OnCatalogPromotion}

  ^FROM

  ^{(SELECT DISTINCT}

  ^{Product.ProductID, Product.Name, SUBSTRING(Product.Description, 1, @DescriptionLength) + '...' AS Description, Product.Price,}

  ^{Product.Image1FileName, Product.Image2FileName, Product.OnDepartmentPromotion, Product.OnCatalogPromotion}

  ^{FROM Product INNER JOIN}

  ^{ProductCategory}

  ^{ON Product.ProductID = ProductCategory.ProductID INNER JOIN}

  ^Category

  ^{ON ProductCategory.CategoryID = Category.CategoryID}

  ^{WHERE (Product.OnDepartmentPromotion = 1) AND (Category.DepartmentID = @DepartmentID)) AS ProductOnDepPr}

  ^{-- return the total number of products using an OUTPUT variable}

  ^SELECT

  ^{@HowManyProducts = COUNT(ProductID) FROM Products}

  ^{-- extract the requested page of products}

  ^SELECT

  ^{ProductID, Name, Description, Price, Image1FileName,}

  ^{Image2FileName, OnDepartmentPromotion, OnCatalogPromotion}

  ^FROM

  ^@Products

  ^WHERE

  ^{RowNumber > (@PageNumber - 1) * @ProductsPerPage}

  ^{AND RowNumber <= @PageNumber * @ProductsPerPage}

  _Thanks

  _Tom

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  November 29, 2006 at 1:33 pm

  #674794

   ^{-- return the total number of products using an OUTPUT variable}

  ^{SELECT @HowManyProducts = COUNT(ProductID) FROM Products}

  ^{Shouldn't you be returning the COUNT from the temp table @Products ?}

• Tom Bathgate

  SSC Journeyman

  Points: 82

  More actions

  November 29, 2006 at 1:54 pm

  #674795

  I dont' follow you?

  Doesn't this pass the value of count to the @HowManyProducts variable?

• John Beggs

  SSCommitted

  Points: 1921

  More actions

  November 29, 2006 at 4:13 pm

  #674821

  I believe there is a typo here, and "Products" should actually be @Products.

  However, I believe a better solution is to use @@ROWCOUNT.

  Select @HowManyProducts = @@ROWCOUNT

  Why hit that table again if you don't have to?

  I am curious, why are you using a subquery here?  I don't see any reason to use a subquery here.  I also am curious why you are using the DISTINCT here?  Granted I don't know your data, but it doesn't seem a good place for it.  Not to mention it may cause some query plan issues...

  I would also write the where clause as

  WHERE  RowNumber/(@ProductsPerPage + 1) = @PageNumber

  but this is really personal preferrence and has no bearing on performance, as there are no indexes on table variables anyway.

  If I can suggest a totally different approach, is there a reason you are not doing the paging in your front end application?  If you are using ASP in any of it's forms, you can easily implement paging by simply setting the <recordset>.PageSize to the correct amount and then set the AbsolutePage.  There is a little more to it, but that is the basics.

  I am not a fan of hitting the database repeatedly to return the same dataset, so I tend to do some caching in the front end.  This may not be good in your environment, so choose what works for you.

• Tom Bathgate

  SSC Journeyman

  Points: 82

  More actions

  November 29, 2006 at 4:26 pm

  #674824

  I fixed the typo but that made no difference. There is still nothing coming from this.

  I am learning this from a book so i don't really know any better, but once I have learnt I can play around with this.

   

  This is the code:

   

  ALTER PROCEDURE

  GetProductsOnDepartmentPromotion

  (@DepartmentID

  INT,

  @DescriptionLength

  INT,

  @PageNumber

  INT,

  @ProductsPerPage

  INT,

  @HowManyProducts

  INT OUTPUT)

  AS

  -- declare a new TABLE variable

  DECLARE

  @Products TABLE

  (Row

  SMALLINT IDENTITY (1,1) NOT NULL,

  ProductID

  INT,

  Name VARCHAR(50),

  Description

  VARCHAR(5000),

  Price

  MONEY,

  Image1FileName

  VARCHAR(50),

  Image2FileName

  VARCHAR(50),

  OnDepartmentPromotion

  BIT,

  OnCatalogPromotion

  BIT)

  -- populate the table variable with the complete list of products

  INSERT INTO

  @Products

  SELECT

  ProductID, Name, SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion

  FROM

  (

  SELECT DISTINCT Product.ProductID, Product.Name, SUBSTRING(Product.Description, 1, @DescriptionLength) + '...' AS Description, Product.Price, Product.Image1FileName, Product.Image2FileName, Product.OnDepartmentPromotion, Product.OnCatalogPromotion

  FROM Product INNER JOIN

  ProductCategory ON Product.ProductID = ProductCategory.ProductID INNER JOIN Category ON ProductCategory.CategoryID = Category.CategoryID

  WHERE

  (Product.OnDepartmentPromotion = 1) AND (Category.DepartmentID = @DepartmentID )

  )

  AS ProductOnDepPr

  -- return the total number of products using an OUTPUT variable

  SELECT

  @HowManyProducts = COUNT(ProductID) FROM @Products

  -- extract the requested page of products

  SELECT

  ProductID, Name, Description, Price, Image1FileName,Image2FileName, OnDepartmentPromotion, OnCatalogPromotion

  FROM

  @Products

  WHERE

  Row > (@PageNumber - 1) * @ProductsPerPage

  AND Row <= @PageNumber * @ProductsPerPage

  I am really clueless here and have no idea at all how to fix this.

  Thanks

  Tom