mssql查询分页存储过程SpPageOrder/proc_wuhan_getpages )

@fldName varchar(255),   -- 显示字段名
@OrderfldName varchar(255), -- 排序字段名
@PageSize int = 10,   -- 页尺寸
@PageIndex int = 1,   -- 页码
@IsReCount bit = 0,   -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0,   -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100)   -- 临时变量(查询条件过长时可能会出错，可修改100为1000)
declare @strOrder varchar(400) -- 排序类型

if @OrderType != 0
begin
set @strTmp = (select min
set @strOrder = order by + @OrderfldName + desc
end
else
begin
set @strTmp = (select max
set @strOrder = order by + @OrderfldName + asc
end

set @strSQL = select top + str(@PageSize) + + @fldName + from [
+ @tblName + ] where [ + @OrderfldName + ] + @strTmp + ([
+ @OrderfldName + ]) from (select top + str((@PageIndex-1)*@PageSize) + [
+ @OrderfldName + ] from [ + @tblName + ] + @strOrder + ) as tblTmp)
+ @strOrder

if @strWhere !=
set @strSQL = select top + str(@PageSize) + + @fldName + from [
+ @tblName + ] where [ + @OrderfldName + ] + @strTmp + ([
+ @OrderfldName + ]) from (select top + str((@PageIndex-1)*@PageSize) + [
+ @OrderfldName + ] 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) + + @fldName + from [
+ @tblName + ] + @strTmp + + @strOrder
end

if @IsReCount != 0
set @strSQL = @strSQL+ select count(1) as Total from [ + @tblName + ]

if @strWhere!=
set @strSQL = @strSQL+ where + @strWhere
exec (@strSQL)
GO
----调用方法，调用后生成两张表第一张表中有数据，第二张表就有全部记录数 ds.Tables[1].Rows[0]["Total"];
SpPageOrder booktype,*,typeid ,4 ,1 ,1 ,1

 

 

 

--名称：创建分页存储过程 
--时间：2011-07-04
--作者：wuhan 吴晗
create proc proc_wuhan_getpages    --创建存储过程
@pageSize int,        --页大小
@currentPage int,       --当前页（第几页） 
@tableName nvarchar(100),     --表名
@columns  nvarchar(1000),     --字段名
@condition nvarchar(100),     --查询条件
@orderType int,        --排序类型（0为升序，1为降序）
@orderColumn nvarchar(100),     --排序字段
@premaryKeyColumn nvarchar(100)    --主键字段名称
as
begin
 declare @strSqlStatement nvarchar(2000)   --存储过程最后执行的语句
 declare @chartype nvarchar(10)
 begin
  if @condition=       --查询条件为空  
  begin
   if @orderType=0       --升序
   begin
    set @chartype= asc
    set @strSqlStatement = select top +ltrim(@pageSize) + +@columns + from + @tableName+ where ( + @premaryKeyColumn+ not in (select Top + convert(char,@pageSize *(@currentPage-1))+ @premaryKeyColumn+ from + @tableName + order by +@orderColumn + @chartype  +)) order by +@orderColumn+ @chartype
   end
   else if @orderType=1
   begin
    set @chartype= desc    --降序
    set @strSqlStatement = select top +ltrim(@pageSize) + +@columns + from + @tableName+ where ( + @premaryKeyColumn+ not in (select Top + convert(char,@pageSize *(@currentPage-1))+ @premaryKeyColumn+ from + @tableName + order by +@orderColumn + @chartype  +)) order by +@orderColumn+ @chartype
   end
  end
  else if @condition!=       --查询条件不为空
  begin
   if @orderType=0       --升序
   begin
    set @chartype= asc
    set @strSqlStatement = select top +ltrim(@pageSize) + +@columns + from + @tableName+ where (+ @premaryKeyColumn+ not in (select Top + convert(char,@pageSize *(@currentPage-1))+ @premaryKeyColumn+ from + @tableName + order by +@orderColumn + @chartype  +)and + @condition +) order by +@orderColumn+ @chartype
   end
   else if @orderType=1     
   begin
    set @chartype= desc    --降序
    set @strSqlStatement = select top +ltrim(@pageSize) + +@columns + from + @tableName+ where ( + @premaryKeyColumn+ not in (select Top + convert(char,@pageSize *(@currentPage-1))+ @premaryKeyColumn+ from + @tableName + order by +@orderColumn + @chartype+) and + @condition +)order by +@orderColumn+ @chartype
   end
  end
 end
 --print @strSqlStatement     --输出最后要执行的语句
 exec (@strSqlStatement)
end

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset 在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了上面这样一个功能。