分页存储过程 BY Bndy

-- Author    Bndy
-- QQ/MSN    81795705/bndy5337@msn.com
-- Email    bndy5337@163.com
-- HomePage    http://www.bndy.net

alter PROCEDURE [dbo].[up_Pagination]
    @tableName nvarchar(50),
    @primaryKey nvarchar(20),
    @selectFileds nvarchar(100) = N'*',
    @condition nvarchar(1000) = N'1>0',
    @sortString nvarchar(100) = N'ID DESC',
    @pageIndex int = 0,
    @pageSize int = 10
AS

BEGIN
    SET NOCOUNT ON;

    IF @selectFileds = '' OR @selectFileds IS NULL
        SET @selectFileds = N'*'

    IF @condition = '' OR @condition IS NULL
        SET @condition = N'1>0'

    IF @sortString = '' OR @sortString IS NULL
        SET @sortString = N' '+ @primaryKey + ' desc'

    ELSE
        IF @primaryKey <> '' AND @primaryKey IS NOT NULL AND CHARINDEX(@primaryKey + ' ', @sortString, 0) < 0
            SET @sortString = @sortString + N', '+ @primaryKey + ' desc'

    DECLARE @sql nvarchar(4000)
    SET @sql = N'if (SELECT OBJECT_ID(''#tmpTable'')) IS NOT NULL DROP TABLE #tmpTable;'

    SET @sql = @sql + (N'SELECT *, ROW_NUMBER() OVER(ORDER BY ' + @sortString + ' ) AS ROWNUMBER INTO #tmpTable FROM ' + @tableName + ' WHERE ' + @condition + ' ORDER BY ' + @sortString + '') + ';'

    IF @pageIndex = 0
        SET @sql = @sql + N'SELECT TOP ' + rtrim(@pageSize) + ' ' + @selectFileds + ' FROM #tmpTable ORDER BY ' + @sortString
    ELSE
        SET @sql = @sql + N'SELECT TOP ' + rtrim(@pageSize) + ' ' + @selectFileds + ''
            + ' FROM #tmpTable'
                + ' WHERE ROWNUMBER > ('
                    + 'SELECT MAX(ROWNUMBER) FROM ('
                        + 'SELECT TOP ' + rtrim((@pageIndex) * @pageSize) + ' ROWNUMBER FROM #tmpTable ORDER BY ' + @sortString
                        + ') AS tbl'
                    + ')'
            + ' ORDER BY ' + @sortString
   
    exec (@sql)

    -- 返回总记录数
    declare @rowCount int, @sqlRowCount nvarchar(4000)
    set @sqlRowCount = 'SELECT @rowCount = COUNT(*) FROM ' + @tableName + ' WHERE ' + @condition
    exec sp_executesql @sqlRowCount, N'@rowCount int output', @rowCount output
    return @rowCount

END