[Summary★Paging stored procedures: Several paging stored procedures

1. Use the max/min subquery: view code

After testing, if the data in the sorting field is the same, the retrieved data is incorrect.

Ii. Use the not in subquery: view code

Alter procedure [DBO]. [proc_pager]
@ Tablename nvarchar (255), -- table name, for example, 'xtest'
@ Fieldname nvarchar (1000) = '*', -- the column to be returned is 'xname, xdemo'
@ Pkname nvarchar (50) = 'id', -- primary key name
@ Orderfield nvarchar (255) = '', -- Name of the sorted field, for example, 'order by id desc'
@ Strwhere nvarchar (1500) = '', -- Query condition (Note: Do not add where), for example, 'xname like ''' % 222 name % '''
@ Pageindex Int = 1, -- page number, for example, 2
@ Pagesize Int = 20, -- number of records per page, for example, 20
@ Isreturncount bit = 0 -- if the value is not 0, statistics are collected. If the value is 0, no statistics are collected. (Statistics affect efficiency)
As

Declare @ SQL nvarchar (4000)
Declare @ sqltemp nvarchar (1000)

Set @ SQL = 'from' + @ tablename
Set @ strwhere = 'where 1 = 1' + @ strwhere
Set @ SQL = @ SQL + @ strwhere

If (@ orderfield! = '') Set @ orderfield = 'ORDER BY' + @ orderfield

-- If @ strwhere! = ''Set @ strwhere = 'where 1 = 1' + @ strwhere

If (@ pageindex> 1)
Begin
Set @ sqltemp = @ pkname + 'not in (select top' + Cast (@ pagesize * (@ PageIndex-1) as nvarchar) + ''+ @ pkname +'' + @ SQL
If (@ orderfield! = '')
Set @ sqltemp = @ sqltemp + ''+ @ orderfield
Set @ sqltemp = @ sqltemp + ')'
Set @ SQL = 'select top '+ Cast (@ pagesize as nvarchar) + ''+ @ fieldname +'' + @ SQL
Set @ SQL = @ SQL + 'and' + @ sqltemp
End
Else
Set @ SQL = 'select top '+ Cast (@ pagesize as nvarchar) + ''+ @ fieldname +'' + @ SQL

If (@ orderfield! = '')
Set @ SQL = @ SQL + ''+ @ orderfield



If (@ isreturncount! = 0)
Set @ SQL = @ SQL + 'select count (1) from' + @ tablename + @ strwhere

Exec (@ SQL)

If the page number is large, the efficiency is relatively low, and the data size is small, it does not matter. Of course, we can further optimize it on this basis.

3. Use row_num:

First, row_num is a new attribute from SQL2000, so it is only applicable

View code

Create procedure [DBO]. [proc_pager] @ tables nvarchar (max), -- table name, supporting multiple tables @ fields nvarchar (max) = '*', -- Return field @ nvarchar (max) = '', -- sorting field (desc asc needs to be added), supports multi-column sorting @ pagesize int, -- page size @ pageindex int, -- page index @ condition nvarchar (max) = ''-- Query condition (without where, starting with and) asdeclare @ SQL nvarchar (max) declare @ startindex intdeclare @ endindex intset @ order = 'ORDER BY' + @ orderset @ startindex = @ pagesize * (@ PageIndex-1) + 1 Set @ endindex = @ pagesize * (@ pageindex) if @ condition is null set @ condition = ''set @ condition = 'where 1 = 1' + @ condition set @ SQL = 'select * from (select row_number () over ('+ @ order +') as rowindex, '+ @ fields + 'from' + @ tables + @ condition + ') temptable where rowindex between '+ Cast (@ startindex as nvarchar) + 'and' + Cast (@ endindex as nvarchar) exec (@ SQL)

 

Summary

With the popularity of sql05 08 and later versions, the discussion of paging stored procedures has gradually become a history. For 05 +, you can use row_num to easily implement paging query.