22,209
社区成员
发帖
与我相关
我的任务
分享
declare @sql1 varchar(200)
set @sql1='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '
declare @sql2 varchar(50)
set @sql2=') AS rownumber,'
declare @sql3 varchar(10)
set @sql3=' FROM '
declare @sql4 varchar(100)
set @sql4=') AS tempdt WHERE rownumber BETWEEN '+@startRow+' AND '+@endRow
/*执行查询语句,返回查询结果*/
exec
(
@sql1+@OrderBy+@sql2+@Fields+@sql3+@Table+@Where+@sql4
)
create PROCEDURE [dbo].[proc_DataPagination]
(
@Table nvarchar(1000),--表名,支持多表联查
@Fields varchar(2000) = N'*',--字段名
@Where nvarchar(4000) = N'',--where条件,不需要加where
@OrderBy nvarchar(500) = N'',--排序条件,不需要加order by
@CurrentPage int = 1, --当前页,从1开始,不是0
@PageSize int = 10,--每页显示多少条数据
@GetCount int =0,--获取的记录总数,0则获取记录总数,不为0则不获取
@Count int = 0 output--总数
)
AS
BEGIN
--没有提供排序字段,默认主键排序
if @OrderBy is null or @OrderBy=''
begin
declare @tempTable varchar(200)
--多表联查如果没有提供排序字段,自动找第一个表的主键进行排序
if charindex(' on ',@Table)>0
set @tempTable=substring(@Table,0,charindex(' ',@Table))
else if charindex(',',@Table)>0
begin
set @tempTable=substring(@Table,0,charindex(',',@Table))
--如果有别名如Article a,User u
if(charindex(' ',@tempTable)>0)
set @tempTable=substring(@tempTable,0,charindex(' ',@tempTable))
end
else
set @tempTable=@Table--单表查询
--查询表是否存在
if not exists(select * from sysobjects where [name]=@tempTable)
begin
raiserror('查询表%s不存在',12,12,@tempTable)
return
end
--查询排序主键
select @OrderBy=d.name from sysindexes a,sysobjects b,sysindexkeys c,syscolumns d
where c.id = object_id(@tempTable) and c.id = b.parent_obj
and a.name = b.name and b.xtype= 'PK ' and a.indid = 1 and d.colid = c.colid and d.id = c.id
--如果没有主键,如视图
if @OrderBy is null or @OrderBy = ''
begin
raiserror('%s必须提供排序字段',12,12,@tempTable)
return
end
end
--分页大小
if @PageSize < 1
set @PageSize=10
--默认当前页
if @CurrentPage < 1
set @CurrentPage = 1
--选取字段
if @Fields is null or @Fields = ''
set @Fields='*'
--过滤条件
if @Where is null or @Where=''
set @Where=''
else
set @Where=' WHERE '+@Where
/*设置分页参数*/
declare @startRow varchar(50),@endRow varchar(50)
set @startRow = cast(((@CurrentPage - 1)*@PageSize + 1) as nvarchar(50))
set @endRow = cast(@CurrentPage*@PageSize as nvarchar(50))
declare @sql nvarchar(max)
set @sql='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@OrderBy+') AS rownumber,'+@Fields+
' FROM '+@Table+@Where+') AS tempdt WHERE rownumber BETWEEN '+@startRow+' AND '+@endRow
print len(@sql)
print @sql
exec(@sql)
/*
如果@GetCount=0,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,
把总页数传回给存储过程,避免再次计算总页数,当数据量很大时,select count(*)速度也要几秒钟)
*/
if(@GetCount=0)
begin
declare @strsql nvarchar(1200)
set @strsql='SELECT @i=COUNT(*) FROM '+@Table+@Where
execute sp_executesql @strsql,N'@i int out',@Count OUT--返回总记录数
end
else
set @Count=@GetCount
END
测试sql语句
declare @Count int
exec [proc_DataPagination] 'SL_User','UserId,UserName,RealName,IdentificationCard,Telphone,HomePhone,Email,LastLoginTime,UserStateId,LoginTotalCount','InTheRecycleBin=0 and (charindex('',''+''60''+'','','',''+ClassIds+'','')>0 or charindex('',''+''61''+'','','',''+ClassIds+'','')>0 or charindex('',''+''62''+'','','',''+ClassIds+'','')>0 or charindex('',''+''63''+'','','',''+ClassIds+'','')>0 or charindex('',''+''64''+'','','',''+ClassIds+'','')>0 or charindex('',''+''65''+'','','',''+ClassIds+'','')>0 or charindex('',''+''66''+'','','',''+ClassIds+'','')>0 or charindex('',''+''77''+'','','',''+ClassIds+'','')>0 or charindex('',''+''78''+'','','',''+ClassIds+'','')>0 or charindex('',''+''79''+'','','',''+ClassIds+'','')>0 or charindex('',''+''80''+'','','',''+ClassIds+'','')>0 or charindex('',''+''81''+'','','',''+ClassIds+'','')>0 or charindex('',''+''82''+'','','',''+ClassIds+'','')>0 or charindex('',''+''83''+'','','',''+ClassIds+'','')>0 or charindex('',''+''84''+'','','',''+ClassIds+'','')>0 or charindex('',''+''85''+'','','',''+ClassIds+'','')>0 or charindex('',''+''86''+'','','',''+ClassIds+'','')>0 or charindex('',''+''87''+'','','',''+ClassIds+'','')>0 or charindex('',''+''88''+'','','',''+ClassIds+'','')>0 or charindex('',''+''89''+'','','',''+ClassIds+'','')>0 or charindex('',''+''90''+'','','',''+ClassIds+'','')>0 or charindex('',''+''91''+'','','',''+ClassIds+'','')>0 or charindex('',''+''92''+'','','',''+ClassIds+'','')>0 or charindex('',''+''93''+'','','',''+ClassIds+'','')>0) and userid=201 and userid=482 and userid=483 and userid=488 and userid=530 and userid=531 and userid=532','',1,20,0,@Count output
select @Count
declare @sql nvarchar(max)
set @sql='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@OrderBy+') AS rownumber,'+@Fields+
' FROM '+@Table+@Where+') AS tempdt WHERE rownumber BETWEEN '+@startRow+' AND '+@endRow
print len(@sql)
print @sql
exec sp_executesql @sql