SqlServer 分页存储过程

发布时间：2020-12-12 13:30:49 所属栏目：MsSql教程来源：网络整理

导读：create proc [dbo].[proc_Opinion_BaseInfo] @TableName varchar(4000),@PkField varchar(100),@PageIndex int=1,@PageSize int=10,@SqlWhere nvarchar(4000),@RowCount bigint output,@PageCount bigint output as ?if(@SqlWhere='1')? ?set @SqlWhere = '1

create proc [dbo].[proc_Opinion_BaseInfo] @TableName varchar(4000),@PkField varchar(100),@PageIndex int=1,@PageSize int=10,@SqlWhere nvarchar(4000),@RowCount bigint output,@PageCount bigint output as ?if(@SqlWhere='1')? ?set @SqlWhere = '1=1' ?declare @sql nvarchar(4000),@start int,@end int ? ?set @sql='select * from (select Row_NUMBER() OVER(order by '+@PkField+' desc) rowId,* from '+@TableName+' where '+@SqlWhere ?set @start = (@PageIndex-1)*@PageSize+1 ?set @end = @start+@PageSize-1 ?set @sql = @sql + ') t where rowId between '+CAST(@start as varchar(20))+' and ' +CAST(@end as varchar(20)) ?exec (@sql) ?set @sql = 'select @RowCount=count(1) from '+@TableName+' where '+@SqlWhere ?exec sp_executesql @sql,N'@RowCount bigint OUTPUT',@RowCount OUTPUT if(@RowCount%@PageSize=0) begin set @PageCount = @RowCount / @PageSize end else begin set @PageCount = @RowCount / @PageSize +1 end

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!