分页|数据|显示
这是经我该写后的存储过程
---------------------------------------------------------
ALTER       PROCEDURE usp_GetRecordFromPage
    tblName       varchar(1000),        -- 表名
    SelectFieldName    varchar(4000),              -- 要显示的字段名(不要加select)
    strWhere       varchar(4000),              -- 查询条件(注意: 不要加 where)
    OrderFieldName      varchar(255),               -- 排序索引字段名
    PageSize       int ,                 -- 页大小
    PageIndex      int = 1,                  -- 页码
    iRowCount      int output,                 -- 返回记录总数
    OrderType      bit = 0                  -- 设置排序类型, 非 0 值则降序
          
AS

declare strSQL    varchar(4000)       -- 主语句
declare strTmp    varchar(4000)        -- 临时变量
declare strOrder  varchar(400)        -- 排序类型
declare strRowCount    nvarchar(4000)      -- 用于查询记录总数的语句

set OrderFieldName=ltrim(rtrim(OrderFieldName))

if OrderType != 0
begin
    set strTmp = '<(select min'
    set strOrder = ' order by ' + OrderFieldName +' desc'
end
else
begin
    set strTmp = '>(select max'
    set strOrder = ' order by ' + OrderFieldName +' asc'
end

set strSQL = 'select top ' + str(PageSize) + SelectFieldName+' from '
    + tblName + ' where ' + OrderFieldName + strTmp + '('
    + right(OrderFieldName,len(OrderFieldName)-charindex('.',OrderFieldName)) + ') from (select top ' + str((PageIndex-1)*PageSize)
    + OrderFieldName + ' from ' + tblName  + strOrder + ') as tblTmp)'
    + strOrder

if strWhere != ''
    set strSQL = 'select top ' + str(PageSize) + SelectFieldName+' from '
        + tblName + ' where ' + OrderFieldName + strTmp + '('
        + right(OrderFieldName,len(OrderFieldName)-charindex('.',OrderFieldName)) + ') from (select top ' + str((PageIndex-1)*PageSize)
        + OrderFieldName + ' from ' + tblName + ' where ' + strWhere + ' '
        + strOrder + ') as tblTmp) and ' + strWhere + ' ' + strOrder

if PageIndex = 1
begin
    set strTmp = ''
    if strWhere != ''
        set strTmp = ' where ' + strWhere

    set strSQL = 'select top ' + str(PageSize) + SelectFieldName+' from '
        + tblName + strTmp + ' ' + strOrder
end

exec(strSQL)
 
if strWhere!=''
begin
  set strRowCount = 'select iRowCount=count(*) from ' + tblName+' where '+strWhere
end
else
begin
  set strRowCount = 'select iRowCount=count(*) from ' + tblName
end

exec sp_executesql strRowCount,N'iRowCount int out',iRowCount out