经典的翻页存储过程[推荐]

最新推荐文章于 2021-11-15 11:34:21 发布

weixin_34391445

最新推荐文章于 2021-11-15 11:34:21 发布

阅读量108

点赞数

本文分享了一个实用的SQL存储过程，用于实现高效分页查询。该存储过程通过动态SQL和临时变量实现了灵活的表名、字段选择及排序等功能，提高了查询效率。

最近找到一很好的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

/**//*定义局部变量*/

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