关于存储过程分页的方法

 

看到这样的代码：觉得还不错。分享一下。

CREATE PROCEDURE dbo.hotel_food_GetPaged
(

    @WhereClause varchar (2000)  ,

    @OrderBy varchar (2000)  ,

    @PageIndex int   ,

    @PageSize int   
)
AS


                
                BEGIN
                DECLARE @PageLowerBound int
                DECLARE @PageUpperBound int
                
                -- 设置范围
                SET @PageLowerBound = @PageSize * @PageIndex
                SET @PageUpperBound = @PageLowerBound + @PageSize

                -- Create a temp table to store the select results
                Create Table #PageIndex
                (
                    [IndexId] int IDENTITY (1, 1) NOT NULL,
                    [wfj_food_id] int 
                )
                
                -- Insert into the temp table
                declare @SQL as nvarchar(4000)
                SET @SQL = 'INSERT INTO #PageIndex (wfj_food_id)'
                SET @SQL = @SQL + ' SELECT'
                IF @PageSize > 0
                BEGIN
                    SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
                END
                SET @SQL = @SQL + ' [wfj_food_id]'
                SET @SQL = @SQL + ' FROM dbo.[hotel_food]'
                IF LEN(@WhereClause) > 0
                BEGIN
                    SET @SQL = @SQL + ' WHERE ' + @WhereClause
                END
                IF LEN(@OrderBy) > 0
                BEGIN
                    SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
                END
                
                -- Populate the temp table
                exec sp_executesql @SQL

                -- Return paged results
                SELECT O.[wfj_food_id], O.[wfj_food_name], O.[wfj_food_address], O.[wfj_food_class], O.[wfj_food_type], O.[wfj_food_pic], O.[wfj_food_price1], O.[wfj_food_price2], O.[wfj_food_price3], O.[wfj_food_introj], O.[wfj_food_intro], O.[wfj_food_ispass], O.[wfj_food_data], O.[wfj_food_addname], O.[wfj_food_hits], O.[wfj_food_order], O.[wfj_food_admin]
                FROM
                    dbo.[hotel_food] O,
                    #PageIndex PageIndex
                WHERE
                    PageIndex.IndexID > @PageLowerBound
                    AND O.[wfj_food_id] = PageIndex.[wfj_food_id]
                ORDER BY
                    PageIndex.IndexID
                
                -- get row count
                SET @SQL = 'SELECT COUNT(*) as TotalRowCount'
                SET @SQL = @SQL + ' FROM dbo.[hotel_food]'
                IF LEN(@WhereClause) > 0
                BEGIN
                    SET @SQL = @SQL + ' WHERE ' + @WhereClause
                END
                exec sp_executesql @SQL
            
                END
            


GO

有哪位仁兄能解释一下用法吗？最好能提供点例子。