分页存储过程

/// <summary>
/// 获取分页数据
/// readType: 1-数据列表,2-记录总数,3-两者
/// </summary>
public static DataTable GetPagedList(string connectionString, ref Int64 recordCount, Int64 pageSize, Int64 pageIndex, string tableName, string keyName, string fieldName, string orderString, string whereString, int readType)
{
    string spName = "SP_GetPagedList";
    SqlParameter[] parms = new SqlParameter[]
    {
        new SqlParameter("recordCount",SqlDbType.BigInt),
        new SqlParameter("tableName",SqlDbType.VarChar,4000),
        new SqlParameter("keyName",SqlDbType.VarChar,200),
        new SqlParameter("fieldName",SqlDbType.VarChar,2000),
        new SqlParameter("pageSize",SqlDbType.BigInt),
        new SqlParameter("pageIndex",SqlDbType.BigInt),
        new SqlParameter("orderString",SqlDbType.VarChar,200),
        new SqlParameter("whereString",SqlDbType.VarChar,1000),
        new SqlParameter("readType",SqlDbType.Int,1000),
    };
    parms[0].Direction = ParameterDirection.Output;
    parms[1].Value = tableName;
    parms[2].Value = keyName;
    parms[3].Value = fieldName;
    parms[4].Value = pageSize;
    parms[5].Value = pageIndex;
    parms[6].Value = orderString;
    parms[7].Value = whereString;
    parms[8].Value = readType;


    DataTable dt = SqlHelper.ExecuteDataTable(connectionString, CommandType.StoredProcedure, spName, parms);
    recordCount = int.Parse(parms[0].Value.ToString());
    return dt;

}

/*分页存储过程*/

IF OBJECT_ID('SP_GetPagedList','P') IS NOT NULL
DROP PROCEDURE SP_GetPagedList
GO


CREATE PROCEDURE [dbo].[SP_GetPagedList]
    @recordCount BIGINT OUTPUT, --记录总数
    @tableName VARCHAR(4000), --表名(必需)
    @fieldName VARCHAR(2000) = '*', --显示字段
    @pageSize BIGINT = 10, --每页记录数
    @pageIndex BIGINT =1, --当前页
    @orderString VARCHAR(200)='', --排序
    @whereString VARCHAR(1000) = '', --WHERE条件
    @readtype int --1:数据,2:记录数,3:两者
AS
BEGIN
SET NOCOUNT ON
DECLARE @beginRow BIGINT
DECLARE @endRow BIGINT
DECLARE @NSQL NVARCHAR(4000)
DECLARE @SQL VARCHAR(8000)
DECLARE @whereRow VARCHAR(400)
declare @TmpTableName varchar(255)


SELECT @recordCount=0,@NSQL='',@SQL='',@whereRow=''


IF LTRIM(RTRIM(@fieldName))=''
SET @fieldName='*'


IF LTRIM(RTRIM(@orderString))<>'' 
SET @orderString=' ORDER BY ' + @orderString


IF LTRIM(RTRIM(@whereString))<>'' 
BEGIN
IF UPPER(LEFT(LTRIM(@whereString),3))='AND'
SET @whereString=' WHERE 1=1 ' + @whereString
ELSE
SET @whereString=' WHERE 1=1 AND ' + @whereString
END


IF @pageSize>0 AND @pageIndex>0
BEGIN
SET @beginRow = (@pageIndex - 1) * @pageSize + 1
SET @endRow = @pageIndex * @pageSize
SET @whereRow = ' AND (ROWID BETWEEN ' + CONVERT(VARCHAR(20),@beginRow) + ' AND ' + CONVERT(VARCHAR(20),@endRow) + ') '
END


set @TmpTableName='#TMP_' + REPLACE(CONVERT(VARCHAR(50),NEWID()),'-','')
SET @SQL =  ' SELECT ROWID=IDENTITY(BIGINT,1,1), ' + @fieldName + 
' INTO ' + @TmpTableName +
' FROM ' + @tableName + 
' WHERE 1=1 ' + @whereString + @orderString
EXEC(@SQL)


if (@readtype & POWER(2,0))>0
BEGIN
SET @NSQL = 'SELECT @recordCount = COUNT(1) FROM ' + @TmpTableName
EXEC sp_executesql @NSQL,N'@recordCount INT OUTPUT',@recordCount OUTPUT
END


if (@readtype & POWER(2,1))>0
BEGIN
SET @SQL =  ' SELECT * FROM ' + @TmpTableName +
' WHERE 1=1 ' + @whereRow +
' ORDER BY ROWID'
EXEC(@SQL)
END

IF OBJECT_ID('TEMPDB..' + @TmpTableName, 'U') IS NOT NULL
BEGIN
EXEC('DROP TABLE ' + @TmpTableName)
END
END
GO