SQL Server分页存储过程

过程代码如下

CREATE PROCEDURE PRC_QUERY
(@P_TABLENAME VARCHAR(MAX),
@P_COLUMNS NVARCHAR(2000),
@P_CONDITION NVARCHAR(1000)=NULL,
@P_SORT NVARCHAR(100)=NULL,
@P_DIR VARCHAR(4),
@P_PAGEINDEX INT,
@P_PAGESIZE INT,
@P_TOTALRECORDS INT=0 OUTPUT)
AS 
SET NOCOUNT ON
DECLARE @V_SQL NVARCHAR(MAX)
DECLARE @V_START INT
BEGIN
    --查询总记录数
    SET @V_SQL='SELECT @P_TOTALRECORDS=COUNT(*) FROM '+@P_TABLENAME+' WHERE 1=1 '
    IF @P_CONDITION IS NOT NULL AND @P_CONDITION <>''
    BEGIN
        SET @V_SQL=@V_SQL+@P_CONDITION
    END
    exec sp_executesql @V_SQL,N'@P_TOTALRECORDS INT OUT',@P_TOTALRECORDS OUT    
    
    SET @V_START=(@P_PAGEINDEX-1)*@P_PAGESIZE
    
    SET @V_SQL='SELECT '+@P_COLUMNS+' FROM (SELECT ROW_NUMBER()OVER(ORDER BY TEMP_COLUMN) TEMP_ROW_NUM, * FROM (SELECT TOP '
    SET @V_SQL=@V_SQL+ CAST(@P_PAGESIZE AS VARCHAR(4))+' TEMP_COLUMN=0,'+@P_COLUMNS+' FROM '+@P_TABLENAME+ ' WHERE 1=1 '
    
    IF @P_CONDITION IS NOT NULL AND @P_CONDITION<>''
    BEGIN
        SET @V_SQL=@V_SQL+@P_CONDITION
    END
    
    IF @P_SORT IS NOT NULL AND @P_SORT<>''
    BEGIN
        SET @V_SQL=@V_SQL+' ORDER BY '+@P_SORT+' '+@P_DIR
    END
    
    SET @V_SQL=@V_SQL+')T1)T2 WHERE TEMP_ROW_NUM >'+CAST(@V_START AS VARCHAR(10))
    
    exec sp_executesql @V_SQL
    print @V_SQL 
END

.NET调用代码如下

public DataTable QueryPage(string table, string fields, string condition, Pager pager, ref int totalRecords)
        {
            _conn = new SqlConnection(_connStr);
            _conn.Open();
            _cmd = _conn.CreateCommand();
            _cmd.CommandText = "PRC_QUERY";
            _cmd.CommandType = CommandType.StoredProcedure;
            _cmd.Parameters.Add("P_TABLENAME", SqlDbType.VarChar);
            _cmd.Parameters["P_TABLENAME"].Direction = ParameterDirection.Input;
            _cmd.Parameters["P_TABLENAME"].Value = table;
            _cmd.Parameters.Add("P_COLUMNS", SqlDbType.NVarChar);
            _cmd.Parameters["P_COLUMNS"].Direction = ParameterDirection.Input;
            _cmd.Parameters["P_COLUMNS"].Value = fields;
            _cmd.Parameters.Add("P_CONDITION", SqlDbType.NVarChar);
            _cmd.Parameters["P_CONDITION"].Direction = ParameterDirection.Input;
            _cmd.Parameters["P_CONDITION"].Value = condition;
            _cmd.Parameters.Add("P_SORT", SqlDbType.NVarChar);
            _cmd.Parameters["P_SORT"].Direction = ParameterDirection.Input;
            _cmd.Parameters["P_SORT"].Value = pager.OrderBy;
            _cmd.Parameters.Add("P_DIR", SqlDbType.VarChar);
            _cmd.Parameters["P_DIR"].Direction = ParameterDirection.Input;
            _cmd.Parameters["P_DIR"].Value = pager.OrderType == OrderType.DESC ? "DESC" : "ASC";
            _cmd.Parameters.Add("P_PAGEINDEX", SqlDbType.Int);
            _cmd.Parameters["P_PAGEINDEX"].Direction = ParameterDirection.Input;
            _cmd.Parameters["P_PAGEINDEX"].Value = pager.PageIndex;
            _cmd.Parameters.Add("P_PAGESIZE", SqlDbType.Int);
            _cmd.Parameters["P_PAGESIZE"].Direction = ParameterDirection.Input;
            _cmd.Parameters["P_PAGESIZE"].Value = pager.PageSize;
            _cmd.Parameters.Add("P_TOTALRECORDS", SqlDbType.BigInt);
            _cmd.Parameters["P_TOTALRECORDS"].Direction = ParameterDirection.Output;
            _cmd.Parameters["P_TOTALRECORDS"].Value = 0;

            _dataAdapter = new SqlDataAdapter(_cmd);
            DataTable dt = new DataTable();
            _dataAdapter.Fill(dt);
            totalRecords = int.Parse(_cmd.Parameters["P_TOTALRECORDS"].Value.ToString());
            _conn.Close();
            _conn.Dispose();
            _cmd.Dispose();
            return dt;
        }