【IT168技术文档】

　　最近找到一很好的SQL翻页存储过程，用过都觉得很不错，执行效率也可以，在此推荐给大家.

CREATE proc up_GetTopicList
@a_TableList Varchar(200), --要查询的字段
@a_TableName Varchar(30), --要查询的表名
@a_SelectWhere Varchar(500), --查询限制条件
@a_SelectOrderId Varchar(20), --查询主键
@a_SelectOrder Varchar(50), --排序字段
@a_intPageNo int,
@a_intPageSize int,
@RecordCount int OUTPUT
as
/**//*定义局部变量*/
declare @intBeginID int
declare @intEndID int
declare @intRootRecordCount int
declare @intRowCount int
declare @TmpSelect NVarchar(600)
/**//*关闭计数*/
set nocount on
/**//*求总共根贴数*/
select @TmpSelect = 'set nocount on;
select @SPintRootRecordCount = count(*) from '+@a_TableName+' '+@a_SelectWhere
execute sp_executesql
@TmpSelect,
N'@SPintRootRecordCount int OUTPUT',
@SPintRootRecordCount=@intRootRecordCount OUTPUT
select @RecordCount = @intRootRecordCount
if (@intRootRecordCount = 0) --如果没有贴子，则返回零
return 0
/**//*判断页数是否正确*/
if (@a_intPageNo - 1) * @a_intPageSize > @intRootRecordCount
return (-1)
/**//*求开始rootID*/
set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize + 1
/**//*限制条数*/
select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;
select @SPintBeginID = '+@a_SelectOrderId+' 
from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintRowCount int,@SPintBeginID int OUTPUT',
@SPintRowCount=@intRowCount,@SPintBeginID=@intBeginID OUTPUT
/**//*结束rootID*/
set @intRowCount = @a_intPageNo * @a_intPageSize
/**//*限制条数*/
select @TmpSelect = 'set nocount on;set rowcount @SPintRowCount;
select @SPintEndID = '+@a_SelectOrderId+' 
from '+@a_TableName+' '+@a_SelectWhere+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintRowCount int,@SPintEndID int OUTPUT',
@SPintRowCount=@intRowCount,@SPintEndID=@intEndID OUTPUT
if @a_SelectWhere='' or @a_SelectWhere IS NULL
select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' 
from '+@a_TableName+' where '+@a_SelectOrderId+' between '
else
select @TmpSelect = 'set nocount off;set rowcount 0;select '+@a_TableList+' 
from '+@a_TableName+' '+@a_SelectWhere+' and '+@a_SelectOrderId+' between '
if @intEndID > @intBeginID
select @TmpSelect = @TmpSelect+'@SPintBeginID and @SPintEndID'+' '+@a_SelectOrder
else
select @TmpSelect = @TmpSelect+'@SPintEndID and @SPintBeginID'+' '+@a_SelectOrder
execute sp_executesql
@TmpSelect,
N'@SPintEndID int,@SPintBeginID int',
@SPintEndID=@intEndID,@SPintBeginID=@intBeginID
return(@@rowcount)
--select @@rowcount
GO