/**//* 过程说明:简单分页程序(要么分页,要么统计) 编 写 人:编写时间:*/ CREATE PROCEDURE Page @tblName varchar(255), -- 表名 @fldName varchar(255), -- 字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(1000) = '' , -- 查询条件 (注意: 不要加 where) @FieldsList varchar(255) -- 字段列表(要选出的字段) AS declare @strSQL varchar(1000), -- 主语句 @strTmp varchar(300), -- 临时变量 @strOrder varchar(400) -- 排序类型 set @strTmp = '' set @strOrder = '' --只统计总记录数 if @IsCount != 0 begin if @strWhere != '' set @strTmp = " where (" + @strWhere + ")" set @strSQL = "select count(*) as Total from [" + @tblName + "]" + @strTmp end --不统计总记录,分页(查询表中所有记录) else begin --如果是第一页 if @PageIndex = 1 begin --排序 if @OrderType != 0 set @strOrder = " order by [" + @fldName +"] desc" else set @strOrder = " order by [" + @fldName +"] asc" --条件 if @strWhere != '' set @strTmp = " where (" + @strWhere + ")" set @strSQL = "select top " + str(@PageSize) + " " +@FieldsList + " from [" + @tblName + "]" + @strTmp + " " + @strOrder end --如果不是第一页 else begin --排序 if @OrderType != 0 begin set @strTmp = "<(select min" set @strOrder = " order by [" + @fldName +"] desc" end else begin set @strTmp = ">(select max" set @strOrder = " order by [" + @fldName +"] asc" end set @strSQL = "select top " + str(@PageSize) + " " + @FieldsList + " from [" + @tblName + "] where [" + @fldName + "]" + @strTmp + "([" + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " [" + @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)" + @strOrder --条件 if @strWhere != '' set @strSQL = "select top " + str(@PageSize) + " " + @FieldsList + " from [" + @tblName + "] where [" + @fldName + "]" + @strTmp + "([" + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " [" + @fldName + "] from [" + @tblName + "] where (" + @strWhere + ") " + @strOrder + ") as tblTmp) and (" + @strWhere + ") " + @strOrder end end exec (@strSQL)