if exists(select 1 from sysobjects where name = 'proc_pagination') drop proc proc_pagination go create proc proc_pagination --创建分页存储过程 @page int = 1, -- 页数 @pagesize int = 10, -- 每页的大小 @tableName varchar(100), -- 要查询的表名 @orderFields varchar(255), -- 排序的字段名(一定要) @GetFields varchar(1000) = '*', -- 需要返回的列 @sqlWhere varchar(1500) = '', -- 查询条件(注意: 不要加where,但要加and) @OrderType varchar(4) = 'desc', -- 设置排序类型 @totalrow int output, --查询出多少条数据(输出参数) @totalpage int output --总有多少页(输出参数) as declare @sql varchar(2000),@sumRowSql nvarchar(2000),@ParmDefinition nvarchar(200); set @sumRowSql = N'select @totalRow = count(1) from ' + @tableName + ' where 1 = 1 '+@sqlWhere; --查询的SQL语句 set @ParmDefinition = N'@totalRow int output'; --定义查询SQL语句的参数类型 exec sp_executesql @sumRowSql,@ParmDefinition,@totalRow = @totalrow output --调用执行字符串的系统存储过程,并接收SQL语句中参数的值 set @sql = 'select top ' + convert(varchar(4),@pagesize) + ' ' + @GetFields + ' from ' + @tableName + ' where ' + @orderFields + ' not in (select top ' + convert(varchar(5),@pagesize*(@page-1)) + ' ' + @orderFields + ' from ' + @tableName + ' order by ' + @orderFields + ' ' + @OrderType + ') and 1 = 1 ' + @sqlWhere + ' order by ' + @orderFields + ' ' + @OrderType; --print @sql; exec(@sql); set @totalpage = (@pagesize+@totalrow-1)/@pagesize; --计算分页数 go ---------执行存储过程---------- declare @rows int ,@page int exec proc_pagination @tableName = 'tb_GoodsBasicInfo',@page = 2,@orderFields = 'StyleNumberId',@sqlWhere = 'and 2 = 2',@totalrow = @rows output,@totalpage = @page output select @rows 总条数,@page 总页数