|
存储过程内部原理：
1 先查询返回符合条件的总的记录数，动态计算页面上的显示信息，如符合条件的总记录数，总页数；
2 然后基于健值字段上去做not in的分页查询，性能上还是比较高的；
3 后面给出一个T-SQL的调用示例。
优点：一劳永逸，表名动态、条件动态、分组动态；
/*
@strTable --需要查询的表名或视图名
@strKeyField --关键字段(一般为表的主键字段)
@strFields --返回的字段列表
@strWhere --查询条件(不要加where)
@strOrderBy --排序语句
@strGroupBy --分组查询
@ilPageIndex --所要查询的页数
@ilPageSize --页大小
@ilRecordCount ?--符条件的记录数
@ilPageCount --符合条件的总页数
*/
CREATE PROCEDURE [dbo].[up_page_select]
@strTable varchar(2048),
@strKeyField varchar(512),
@strFields varchar(2048),
@strWhere varchar(2048),
@strOrderBy varchar(2048),
@strGroupBy varchar(2048),
@ilPageIndex int,
@ilPageSize int = 20,
@ilRecordCount int OUTPUT,
@ilPageCount int OUTPUT
--WITH ENCRYPTION
AS
--
-- 分页查询数据
-- 要查询的表中，必须存在一个唯一键，否则无法使用此过程
--
DECLARE @SQL nvarchar(4000),@strFromCluse nvarchar(4000),
@strWhereCluse nvarchar(4000),@strOtherCluse nvarchar(4000)
SET @strFromCluse = ' FROM ' + @strTable
IF (@strWhere IS NOT NULL) AND (@strWhere <> '')
SET @strWhereCluse = ' WHERE ' + @strWhere
ELSE
SET @strWhereCluse = ''
SET @strOtherCluse = ''
IF (@strGroupBy IS NOT NULL) AND (@strGroupBy <> '')
SET @strOtherCluse = @strOtherCluse + ' GROUP BY ' + @strGroupBy
IF (@strOrderBy IS NOT NULL) AND (@strOrderBy <> '')
SET @strOtherCluse = @strOtherCluse + ' ORDER BY ' + @strOrderBy
IF (@strFields IS NULL) OR (@strFields = '')
SET @strFields = '*'
IF (@ilPageIndex <= 0)
SET @ilPageIndex = 1
IF @ilPageSize > 0
BEGIN
DECLARE @cur CURSOR,@keyvalue varchar(100),@i int
--返回符合条件的记录条数
?set @SQL = 'SELECT @sizec=COUNT('+ @strKeyField+')'+ @strFromCluse + @strWhereCluse
EXECUTE sp_executesql @SQL,N'@sizec as int output',@ilRecordCount output
--获取命中条数
SET @ilPageCount = CEILING(@ilRecordCount * 1.0 / @ilPageSize)
DECLARE @KeyList varchar(2000)
SET @i = (@ilPageIndex - 1) * @ilPageSize + 1
--返回符合条件的条件语句
set @KeyList= @strKeyField +?
' IN(SELECT top '+
convert(varchar,@ilPageSize*@ilPageIndex)+
' '+@strKeyField+@strFromCluse+
' where ' +@strKeyField+?
' not in(select top '+
convert(varchar,@ilPageSize*(@ilPageIndex-1))+
' '+@strKeyField + @strFromCluse+' order by ' +
@strKeyField+ ' desc) order by ' +@strKeyField+ ' desc) '
IF (@strWhereCluse = '')
SET @strWhereCluse = ' WHERE ' + @KeyList
ELSE
SET @strWhereCluse = @strWhereCluse + ' AND ' + @KeyList
SET @SQL = 'SELECT ' + @strFields + @strFromCluse + @strWhereCluse + @strOtherCluse
END
ELSE
SET @SQL = 'SELECT ' + @strFields + @strFromCluse + @strWhereCluse + @strOtherCluse
--print @SQL
EXECUTE sp_executesql @SQL
--调用示例
DECLARE @return_value int,@ilRecordCount int,@ilPageCount int EXEC @return_value = [dbo].[up_page_select] @strTable = N'vi_member_list',@strKeyField = N'member_id',@strFields = N'*',@strWhere = N'top_area=1 AND ( m_class in (1,2,3,4,5))',@strOrderBy = N'm_create_datetime desc ',@strGroupBy = '',@ilPageIndex = 3,@ilPageSize = 30,@ilRecordCount = @ilRecordCount OUTPUT,@ilPageCount = @ilPageCount OUTPUT SELECT @ilRecordCount as N'@ilRecordCount',@ilPageCount as N'@ilPageCount' SELECT 'Return Value' = @return_value GO
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
