Source: http://www.jb51.net/article/19936.htm
CREATE PROCEDUREcommonpagination@columns varchar( -),--column names to display, separated by commas@tableName varchar( -),--the name of the table to query@orderColumnName varchar( -),--sorted column names@order varchar( -),--Sort by ASC in ascending order, descending to Desc@where varchar( -),--where condition, if not with the query condition, please use 1=1@pageIndex int,--Current page Index@pageSize int,--page Size (number of records displayed per page)@pageCount int --total pages, output parameters as begin Declare @sqlRecordCount nvarchar( +)--The statement that gets the total number of records Declare @sqlSelect nvarchar( +)--Query Statements Set @sqlRecordCount=N'Select @recordCount =count (*) from' +@tableName + 'where'+ @where Declare @recordCount int --A variable that holds the total number of record bars execsp_executesql@sqlRecordCountN'@recordCount int Output',@recordCountOutput--Dynamic SQL pass -through parameters if(@recordCount % @pageSize = 0)--if the total number of records can be divisible by the page size Set @pageCount = @recordCount / @pageSize --The total number of pages is equal to the total record bar divided by the page size Else --if the total number of records cannot be divisible by the page size Set @pageCount = @recordCount / @pageSize + 1 --The total number of pages is equal to the total record bar divided by the page size plus 1 Set @sqlSelect =N'Select'+@columns+'From (select Row_number () over (order by' +@orderColumnName+' '+@order +') as tempid,* from' +@tableName+'where'+ @where +') as Temptablename where tempid between' +Str((@pageIndex - 1)*@pageSize + 1 ) +' and'+Str(@pageIndex * @pageSize) exec(@sqlSelect)--Execute Dynamic SQLEnd --The following is an example invocationDeclare @pageCount int execcommonpagination'Job_id,job_desc','Jobs','job_id', 'ASC','1=1',2,2,@pageCountOutputSelect 'the total number of pages is:' + Str(@pageCount)
SQL Server universal Paging stored procedure