High-efficiency jsp + mssql Stored Procedure Common Database Paging

High-efficiency jsp + mssql Stored Procedure Common Database Paging

High-efficiency jsp tutorial + mssql Stored Procedure general database tutorial page

Create procedure Ture_Page

@ PageSize int, -- number of rows per page
@ PageIndex int, -- 1 indicates the first page
@ Col varchar (200), -- the field to be displayed
@ Table varchar (200), -- the Table used. If there are multiple entries, the content between the from and where is written.
@ Where varchar (200) = '', -- conditions used
@ OKey varchar (50), -- Sort Field
@ Order varchar (20) = 'asc '-- sorting method

As
Declare @ brief STR varchar (2000)
Set nocount on

Set @ override STR = 'select top'
Set @ brief STR = @ brief STR + convert (nvarchar, @ PageSize)
If @ Order = 'desc' and @ PageIndex> 1
Set @ brief STR = @ brief STR + ''+ @ Col + 'from' + @ Table + 'where' + @ OKey + '<'
Else if @ PageIndex = 1
Set @ brief STR = @ brief STR + ''+ @ Col + 'from' + @ Table + 'where' + @ OKey + '> ='
Else
Set @ brief STR = @ brief STR + ''+ @ Col + 'from' + @ Table + 'where' + @ OKey + '>'
If @ PageIndex> 1
Begin
If @ Order = 'asc'
Set @ brief STR = @ brief STR + '(select max (' + @ OKey + ') from (select top'
Else
Set @ brief STR = @ brief STR + '(select min (' + @ OKey + ') from (select top'
Set @ brief STR = @ brief STR + convert (nvarchar, (@ PageIndex-1) * @ PageSize)
Set @ brief STR = @ brief STR + ''+ @ OKey + 'from' + @ Table + 'ORDER BY' + @ OKey +'' + @ order + ') as t )'
End
Else
Set @ brief STR = @ brief STR + '0'
If @ Where <>''
Set @ brief STR = (@ brief STR + 'and' + @ Where + 'ORDER BY' + @ OKey + ''+ @ order)
Else
Set @ brief STR = (@ brief STR + 'ORDER BY' + @ OKey + ''+ @ order)

Print @ brief Str
Exec (@ brief Str)
Set nocount off
GO %>