Shulou(Shulou.com)05/31 Report--

This article mainly explains the "sqlserver general paging stored procedures", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "sqlserver general paging stored procedures" bar!

The copy code is as follows:

Create proc commonPagination @ columns varchar,-- column names to be displayed, separated by commas @ tableName varchar (100),-- Table names to be queried @ orderColumnName varchar (100),-- sorted column names @ order varchar (50),-- sort mode, ascending order is asc, descending order is desc @ where varchar (100),-- where condition, if there are no query conditions Please use 1 entries 1 @ pageIndex int,-- current page index @ pageSize int,-- page size (number of records displayed per page) @ pageCount int output-- total number of pages Output parameter as begin declare @ sqlRecordCount nvarchar (1000)-statement declare @ sqlSelect nvarchar (1000) to get the total number of records-query statement set @ sqlRecordCount=N'select @ recordCount=count (*) from'+ @ tableName + 'where' + @ where declare @ recordCount int-variable exec sp_executesql @ sqlRecordCount,N'@recordCount int output' that holds the total number of records @ recordCount output-- dynamic sql parameter if (@ recordCount% @ pageSize = 0)-- if the total number of records is divisible by the page size set @ pageCount = @ recordCount / @ pageSize-- the total number of pages is equal to the total number of records divided by the page size else-- if the total number of records is not divisible by the page size set @ pageCount = @ recordCount / @ pageSize + 1-- the total number of pages is equal to the total number of records 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 Sql end go-- the following is a sample call to use pubs go declare @ pageCount int exec commonPagination 'job_id,job_desc','jobs','job_id',' asc','1=1',2,2 The total number of @ pageCount output select 'pages is:' + str (@ pageCount) Thank you for your reading. The above is the content of "sqlserver general paging stored procedure". After the study of this article, I believe you have a deeper understanding of the problem of sqlserver general paging stored procedure, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.