A paging stored procedure that can query the total number of pages and total page count in a table __ stores

if exists (select 1 from sysobjects where name = ' proc_pagination ') drop proc proc_pagination go create proc Proc_paginatio N--Creates a paging stored procedure @page int = 1,--the number of pages @pagesize int = 10, the size of each page @tableName varchar (100), the table name to query @orderFields varchar (255 --The sorted field name (to be sure) @GetFields varchar (1000) = ' * ',--the column to be returned @sqlWhere varchar (1500) = ',--Query condition (Note: Do not add where, but add and) @Or Dertype varchar (4) = ' desc ',--set Sort type @totalrow int output,--How many data (output parameters) are queried @totalpage int output-Total number of pages (output parameters) as Decla Re @sql varchar (), @sumRowSql nvarchar (), @ParmDefinition nvarchar (200); Set @sumRowSql = N ' Select @totalRow = count (1) from ' + @tableName + ' WHERE 1 = 1 ' + @sqlWhere; --Query SQL statement Set @ParmDefinition = N ' @totalRow int output '; --Define the parameter type of the query SQL statement exec sp_executesql @sumRowSql, @ParmDefinition, @totalRow = @totalrow output--invokes the system stored procedure that executes the string. and receive the value of the parameter in the SQL statement set @sql = ' Select top ' + CONVERT (varchar (4), @pagesize) + "+ @GetFields + ' from ' + @tableName + ' wher E ' + @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; --Count the number of pages go---------execute stored procedures----------declare @rows int, @page int exec proc_pagination @tableName = ' Tb_goodsbasicinfo ', @p Age = 2, @orderFields = ' Stylenumberid ', @sqlWhere = ' and 2 = 2 ', @totalrow = @rows output, @totalpage = @page Output SELECT @ Total number of rows, @page total pages