Gridview paging? How does it works? really a sql question

See this article:

http://www.nerdymusings.com/LPMArticle.asp?ID=23

>>For this you can use the RowNumber function<<
That is true providing they are using SQL Server 2005 or higher.
The question was posted in MS SQL Server 2008 zone, however this comment appears to indicate they may be using SQL Server 2000:
I am using mssql 8 express server

Although it was not called the Express Edition back then, so who knows.

the examples, you guys posted, are still using Gridview.  I want to create and use my own table.

would be helpful if there is an example with pages using a table. thanks

I looked at the examples, but I am really confused.  

I would think, the stored procedure should have 3 inputs, my select statement, page number and the page size.

Not sure, how to make the following store procedure look more generalized.  Please help. thanks


CREATE PROCEDURE dbo.GetProductsPaged
(
    @startRowIndex int,
    @maximumRows int
)
AS
    SELECT     ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
               UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
               CategoryName, SupplierName
FROM
   (
       SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
              UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
              (SELECT CategoryName
               FROM Categories
               WHERE Categories.CategoryID = Products.CategoryID) AS CategoryName,
              (SELECT CompanyName
               FROM Suppliers
               WHERE Suppliers.SupplierID = Products.SupplierID) AS SupplierName,
              ROW_NUMBER() OVER (ORDER BY ProductName) AS RowRank
        FROM Products
    ) AS ProductsWithRowNumbers
WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

You can re-write that as follows:

CREATE PROCEDURE dbo.GetProductsPaged
(
    @startRowIndex int,
    @maximumRows int
)
AS

SET NOCOUNT ON;

WITH PagingCTE AS (
       SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
              UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
              (SELECT CategoryName
               FROM Categories
               WHERE Categories.CategoryID = Products.CategoryID) AS CategoryName,
              (SELECT CompanyName
               FROM Suppliers
               WHERE Suppliers.SupplierID = Products.SupplierID) AS SupplierName,
              ROW_NUMBER() OVER (ORDER BY ProductName) AS RowRank
        FROM Products
    )
SELECT	ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
        UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
        CategoryName, SupplierName
FROM	PagingCTE
WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

Select allOpen in new window

This may actually be better:

CREATE PROCEDURE dbo.GetProductsPaged
(
    @startRowIndex int,
    @maximumRows int
)
AS

SET NOCOUNT ON;

WITH PagingCTE AS (
       SELECT p.ProductID, p.ProductName, p.SupplierID, p.CategoryID, p.QuantityPerUnit,
              p.UnitPrice, p.UnitsInStock, p.UnitsOnOrder, p.ReorderLevel, p.Discontinued,
              c.CategoryName,
              s.CompanyName SupplierName,
              ROW_NUMBER() OVER (ORDER BY ProductName) AS RowRank
        FROM Products p
	    INNER JOIN Categories c ON p.CategoryID = c.CategoryID			-- Change this to a LEFT JOIN if more appropriate
	    INNER JOIN Suppliers s ON p.SupplierID = s.SupplierID			-- Change this to a LEFT JOIN if more appropriate
    )
SELECT	ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
        UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
        CategoryName, SupplierName
FROM	PagingCTE
WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

Select allOpen in new window

I am sorry I am still confused.  I would not even know in advance what my select statement would look like.

CREATE PROCEDURE dbo.GetProductsPaged
(
    @startRowIndex int,
    @maximumRows int
    @mySQLstatement Varchar(500) ????????????????????
)
AS

SET NOCOUNT ON;

do not know what to do next?  Is generalized sql statement even possible?

>>Is generalized sql statement even possible?<<
Not without resorting to using Dynamic SQL.  Your best bet is to write a separate Stored Procedure for each case.

thanks, let me make a very simple example, for my understanding,

if I want to run
Select col1 col2 from myTable order by col1  and my pagesize is 50 records

then how would my store procdure would look ? thanks

thanks, I still need to know how to generalized the procedure so I can send sql as a parameter to the stored procedure.