sql2005 分页存储过程

1。tblname 表名可以是连接好的

2。keyfield order by 为row_number() 用的

3. ordertype 0 升序，1降序

4。fields 要返回的字段列表

5。where 条件， 不带WHERE关键字

6，pagesize 每页记录数

7，pageindex 第几页

8，totalcount 总记录数

 

 

 

ALTER PROCEDURE [dbo].[SP_Pagination]
@tblName VARCHAR(255),            -- base table + join table
@keyField VARCHAR(255),            -- order by field
@orderType BIT = 0,                -- 0-Asc, 1-Desc
@fields VARCHAR(1000) = '*',    --
@where VARCHAR(1500) = '',        -- condition, note no 'where'
@pageSize INT = 20,                --
@pageIndex INT = 1,                --
@totalCount INT OUTPUT            --
AS
DECLARE @strSql nVARCHAR(4000)
DECLARE @beginIndex  INT   
DECLARE @endIndex  INT

SET @beginIndex = @pageSize * (@pageIndex - 1) + 1;
SET @endIndex = @pageSize * @pageIndex;

-- get total count
IF @where !=''
    SET @strSql = N'SELECT @totalCount = ISNULL(count(1),0) FROM ' + @tblName + ' WHERE '+@where + ';';
ELSE
    SET @strSql = N'SELECT @totalCount = ISNULL(count(1),0) FROM ' + @tblName + ';';

-- get records by page if pagesize > 0, else get all records
IF @pageSize > 0
BEGIN
    SET @strSql = @strSql + 'SELECT ' + @fields + ' FROM ( SELECT ' + @fields + ',ROW_NUMBER() OVER (ORDER BY ' + @keyField;

    IF(@orderType = 1)
        SET @strSql = @strSql + ' DESC';

    SET @strSql = @strSql + ') AS ROWINDEX FROM ' + @tblName + '';

    IF @where !=''
        SET @strSql = @strSql + ' WHERE ' + @where;

    SET @strSql = @strSql + ') AS tempTable WHERE ROWINDEX BETWEEN ' + CAST (@beginIndex AS VARCHAR ) + ' AND ' + CAST (@endIndex AS VARCHAR ) + ';';
END
ELSE
BEGIN
    SET @strSql = @strSql + 'SELECT ' + @fields + ' FROM ' + @tblName + '';

    IF @where !=''
        SET @strSql = @strSql + ' WHERE ' + @where;

    SET @strSql = @strSql + ';';
END

EXECUTE sp_executesql @strSql,N'@totalCount INT OUTPUT', @totalCount output;