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 总页数
