T-SQL基本语法应用----分页存储过程

      最近闲时学习了一下MS SQL存储过程，个人感觉只要灵活撑握了一门语言，其它语言也能触类旁通，而且每种语言都有详细的

帮助文档，是最好的学习资料。通过两天的学习，写了一个通用的分页存储过程,有详细注释，变量定义、变量赋值、常用语

句、异常处理、....都有所涉及，希望对正在学习T-SQL语言及存储过程的人有所帮助。

 

      1、分页存储过程源码

--------- 分页存储过程 borbby 2009-7-27 -----------------------------
set ANSI_NULLS ON --参照MSDN
set QUOTED_IDENTIFIER ON --参照MSDN
go

create proc pPageRecord
 @TableName nvarchar(128)  --表名,如:[用户表]
 ,@PageSize int = 10 --页大小
 ,@PageIndex int = 1 --当前页号，从1开始
 ,@Fields nvarchar(4000) = N'*' --查询字段名,字段名之间用逗号分隔,如:[用户ID],[用户名]
 ,@OrderFields nvarchar(4000) = NULL --排序字段，如:[用户ID] DESC
 ,@Filter nvarchar(4000) = NULL --查询条件,如:[用户ID] > 10
 ,@Message nvarchar(4000) output --输出信息
 
AS
begin
 if(@TableName is NULL or len(@TableName) = 0)
 begin
  set @Message = N'请指定表名'
  return 0
 end

 if(object_id(@TableName) is NULL)
 begin
  set @Message = N'表名: ' + @TableName + N' 不存在'
  return 0
 end

 ------销毁临时表---------------------------
 if(object_id('##tempPage') is not null)
 begin
  drop table ##tempPage
 end

 if(object_id('##tempPage1') is not null)
 begin
  drop table ##tempPage1
 end
 ------销毁临时表end-------------------------

 declare @RowCount int --记录行数
 declare @UpIndex int --记录下标上限
 declare @LowIndex int --记录下标下限
 declare @sql nvarchar(4000)

 --如果没有指定查询字段,则为查询所有字段
 if(@Fields is null or len(@Fields) = 0)
 begin
  set @Fields = N'*'
 end

 --没指排序字段则默认为第一个主键
 if(@OrderFields is null or len(@OrderFields) = 0)
 begin
  --------------取第一个主键---------------------
  --销毁临时表
  if(object_id(N'#tempPK') is not null)
  begin
   drop table #tempPK
  end

  --为接收主键信息创建临时表
  create table #tempPK(
  a varchar(128),
  b varchar(128),
  c varchar(128),
  d varchar(128),
  e int,
  f varchar(128),
  )
 
  ------如果表名加了中括号(如：[用户表]),则将中括号去掉-----------------
  declare @length int, @StatIndex int
  declare @ch nvarchar(1), @tempTableName nvarchar(128)
  set @tempTableName = @TableName
  set @length = len(@tempTableName)
  set @ch = substring(@tempTableName, 1, 1)
  
  --如果左括号'['存在则右括号']'也必存在,若不同时存在则会导致表名不存在
  if(@ch = N'[')
  begin
   set @length = @length - 1
   set @StatIndex = 2
   set @tempTableName = substring(@tempTableName, @StatIndex, @length)
   set @length = @length - 1
   set @StatIndex = 1
   set @tempTableName = substring(@tempTableName, @StatIndex, @length)
  end
  ------去掉中括号end-------------------------------------------------
  
  insert into #tempPK execute sp_pkeys @tempTableName --取主键信息
  select @OrderFields = d from #tempPK where e = 1 --取第一个主键名
  drop table #tempPK --销毁临时表

  if(@OrderFields is null or len(@OrderFields) = 0)
  begin
   set @Message = N'未找到表: ' + @TableName + N' 主键,请指定排序字段名'
   return 0
  end
  --------------取第一个主键end---------------------
 end

 --------构造一个带有行号编号(RowNumber)的临时记录集##tempPage------------
 declare @tempSQL nvarchar(4000) --用于显示错误信息时使用
 set @sql = N'select ' + @Fields + N',ROW_NUMBER() over (order by ' + @OrderFields + N') as RowNumber into ##tempPage from ' + @TableName
 set @tempSQL = N'select ' + @Fields + N' from ' + @TableName + N' order by '+ @OrderFields
 if(len(@Filter) > 0)
 begin
  set @sql =  @sql + N' where(' + @Filter + N')'
  set @tempSQL = N'select ' + @Fields + N' from ' + @TableName + N' where(' + @Filter + N')' +  N' order by '+ @OrderFields
 end
 begin try
  execute sp_executesql @sql
 end try
 begin catch
  set @Message = N'执行sql语句时出错:' + @tempSQL + N'/r/n错误信息:' + ERROR_MESSAGE()
  return 0
 end catch
 --------构造一个带有行号编号(RowNumber)的临时记录集end----------

 select @RowCount = count(*) from ##tempPage --取记录行数
 
 ------------确定当前查询页下限-----------------------------
 set @PageIndex = @PageIndex -1
 set @LowIndex = @PageSize * @PageIndex + 1
 if(@LowIndex < 1)
 begin
  set @LowIndex = 1
 end

 ------------确定当前查询页上限-----------------------------
 set @UpIndex = @LowIndex + @PageSize - 1
 if(@UpIndex > @RowCount)
 begin
  set @UpIndex = @RowCount
 end

 -------------将满足当前页的记录集放入临时表##tempPage1并销毁临时表##tempPage-----------------
 select * into ##tempPage1 from ##tempPage where (RowNumber >= @LowIndex and RowNumber <= @UpIndex)
 drop table ##tempPage

 -------将行编号行RowNumber删除
 alter table ##tempPage1
  drop column RowNumber
 
 select * from ##tempPage1 --输出当前页记录集
 drop table ##tempPage1 --销毁临时表

 set @Message = N'操作成功'
 return 1
end
go

 

 

2、使用示例
a、缺省调用
declare @Msg nvarchar(4000)
execute  pPageRecord @TableName=N'[用户表]',@Message = @Msg output
select @Msg AS Msg

b、一般调用,每页10条记录，显示第一页
declare @Msg nvarchar(4000)
execute  pPageRecord N'[用户表]',10,1,@Message = @Msg output
select @Msg AS Msg

c、完整调用
declare @Msg nvarchar(4000)
execute  pPageRecord N'[用户表]',10,1,N'[用户ID],[用户名]',N'[[用户ID]]',N'[用户ID]>10',@Message = @Msg output
select @Msg AS Msg

d、无顺传入部分参数
declare @Msg nvarchar(4000)
execute  pPageRecord @TableName=N'[用户表]',@Fields=N'[用户ID],[用户名]',@OrderFields=N'[用户

ID] DESC',@PageSize=10,@PageIndex=1, @Message = @Msg output
select @Msg AS Msg