存储过程分页

存储过程分页
USE [coaleduexamdata]
GO
/****** Object:  StoredProcedure [dbo].[pro_PageCanOrder_2]    Script Date: 12/11/2012 16:43:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pro_PageCanOrder_2]
@tblName nvarchar(255), -- 表名 
@strGetFields nvarchar(1000), -- 需要返回的列 
@fldName nvarchar(255), -- 排序的字段名 
@PageSize int, -- 页尺寸 
@PageIndex int, -- 页码 
@OrderType bit, -- 设置排序类型, 非 0 值则降序 
@strWhere nvarchar(1500), -- 查询条件 (注意: 不要加 where) 
@rowCount int output --查询到的记录数 
AS 

declare @strSQL nvarchar(4000) -- 主语句 
declare @strTmp nvarchar(4000) -- 临时变量 
declare @strOrder nvarchar(4000) -- 排序类型 
declare @strCountTmp nvarchar(4000) --记录数 
declare @timediff datetime --耗时 

begin 
select @timediff = Getdate() 
if @strWhere !='' 
set @strCountTmp = 'select @rowCount = count(*) from [' + @tblName + '] where '+@strWhere
else 
set @strCountTmp = 'select @rowCount = rowCount_tmp from tmp where (table_tmp = ''' + @tblName +''')' 

exec sp_executesql @strCountTmp,N'@rowCount int out',@rowCount out --输出总记录数 

if @OrderType != 0 
begin 
set @strTmp = '<(select min' 
set @strOrder = ' order by ' + @fldName +' desc' 
--如果@OrderType不是0,就执行降序,这句很重要! 
end 

else 
begin 
set @strTmp = '>(select max' 
set @strOrder = ' order by ' + @fldName +' asc' 
end 
if @PageIndex = 1 
begin 
if @strWhere != '' 
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder 
else 
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder 
--如果是第一页就执行以上代码,这样会加快执行速度 
end 

else 
begin 
--以下代码赋予了@strSQL以真正执行的SQL代码 
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' 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) +' '+@strGetFields+ ' 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) 
select datediff(ms,@timediff,GetDate()) as runtime 
存储过程分页

 


作者:望月狼
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利.http://www.cnblogs.com/wangyuelang0526/archive/2012/12/11/2813248.html
上一篇:手动将jar添加到maven仓库中


下一篇:InnoDB的数据页结构