web页面大数据显示离不开分页功能,动态存储过程是常用的方法。存储过程是sql server预编译程序,只需要动态参数即可执行,存储过程中可再次调用其它存储过程,速度快,调用方便,是数据库业务逻辑处理常用方式。给大家介绍一种高效分页动态存储过程。
1,页面调用存储过程:
ALTER proc [dbo].[UserCategoryGet_Page]
@chrNo varchar(50), --主键ID
@chrSearch varchar(1000), --搜索条件 --注意主键ID与搜索条件参数不能同时设定
@CurrIndex int, --当前页面索引 从1开始
@PageSize int, --每页显示数据条数
@order int=1 --每页显示数据条数
as
begin
declare @chrwhere varchar(2000) --查询条件
set @chrwhere= case when isnull(@chrNo,'')='' then '' else +' 1=1 and a.CategoryID='+@chrNo+'' end
+ case when isnull(@chrSearch,'')='' then '' else ' 1=1 '+@chrSearch+'' end
--再次调用存储过程
exec [Paging]
' a.*
', --查询字段
' UserCategory a --表名
', --查询主表(多表)
@chrwhere, --查询条件 (注意: 不要加 where)
'CategoryID', --排序的主键字段名
@order, --设置排序类型, 非 0 值则降序
@PageSize, --每页显示数据条数
@CurrIndex --当前页码
end
2.分页主存储过程 [Paging]:
ALTER PROCEDURE [dbo].[Paging]
@select varchar(2000) = '*', -- 需要返回的列
@table varchar(2000), -- 表名
@where varchar(2000) = '', -- 查询条件 (注意: 不要加 where)
@orderField varchar(255) = '', -- 排序的字段名
@orderType bit = 0, -- 设置排序类型, 非 0 值则降序
@pageSize int, -- 页尺寸
@PageIndex int = 1 -- 页码
AS
declare @strSql varchar(8000) -- 主语句
declare @strSql2 varchar(2000) -- 查询总数的语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @countColumn varchar(50) -- 计数查询字段
-- 判断一下@select是否包含distinct
declare @distinct varchar(20)
set @select = ltrim( @select )
if( ( charindex( 'distinct ', @select ) = 1 ) or ( charindex( 'distinct ', @select ) = 1 ) or ( charindex( 'distinct
', @select ) = 1 ) )
begin
set @select = right( @select, len( @select ) - len( 'distinct') )
set @distinct = 'distinct'
if( charindex( ',', @select ) = 0 )
begin
set @countColumn = 'distinct ' + @select
end
else
begin
set @countColumn = 'distinct ' + left( @select, charindex( ',', @select ) - 1 )
end
end
else
begin
set @distinct = ''
set @countColumn = '1'
end
-- 查询总数语句
if @where !=''
set @strSql2 = 'select count(' + @countColumn + ') as Count from ' + @table + ' where '+ @where
else
set @strSql2 = 'select count(' + @countColumn + ') as Count from ' + @table + ' '
-- 主查询语句
if @orderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @orderField +' desc'
--如果@orderType不是0,就执行降序
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @orderField +' asc'
end
if @PageIndex = 1
begin
if @where != ''
set @strSql = 'select ' + @distinct + ' top ' + str(@pageSize) +' '+@select+ ' from ' + @table + ' where ' + @where + ' ' + @strOrder
else
set @strSql = 'select ' + @distinct + ' top ' + str(@pageSize) +' '+@select+ ' from '+ @table + ' '+ @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
set @strSql = 'select ' + @distinct + ' top ' + str(@pageSize) +' '+@select+ ' from '
+ @table + ' where ' + @orderField + ' ' + @strTmp + '( ID ) from (select ' + @distinct + ' top ' + str((@PageIndex-1)*@pageSize) + ' '+ @orderField + ' ID from ' + @table + ' ' + @strOrder + ') as tblTmp)'+ @strOrder
if @where != ''
set @strSql = 'select ' + @distinct + ' top ' + str(@pageSize) +' '+@select+ ' from '
+ @table + ' where ' + @orderField + ' ' + @strTmp + '( ID ) from (select ' + @distinct + ' top ' + str((@PageIndex-1)*@pageSize) + ' '
+ @orderField + ' ID from ' + @table + ' where ' + @where + ' '
+ @strOrder + ') as tblTmp) and ' + @where + ' ' + @strOrder
end
exec ( @strSql )
exec ( @strSql2 )