Create Proc XPage - Author: Xiao @. EXE - DATE: 2004 Tanabata - Ver: 1.1 @columns varchar (200), - Requires query columns
@TableValue VARCHAR (200), - Table and condition you need to query
@ORDERKEY VARCHAR (20), - Sort field
@IDENTKEY VARCHAR (30) = 'id', - unique identity field
@PageSize Smallint, - Row number of rows per page
@PAGENUMBER Smallint, - The page number to display, starting from 1
@ORDER VARCHAR (4) = 'dec', - Sort Method DESC / ASC
@Result Int Output - Output total record number
AS
Set nocount on
Begin
Declare @MAINSQL As Varchar (1000)
Declare @virtualtable as varchar (1000)
Declare @countable as nvarchar (1000)
Declare @alls as int
Set @countable = 'select @all = count (' @IDENTKEY ')' 'from' @TableValue
EXEC SP_EXECUTESQL @ countable, n '@ All Int output', @ alls output
Set @ result = @ alls
Set @VirtualTable = 'SELECT TOP' CAST ((@ Pagenumber) * @PageSize As Varchar (30)) '' @columns
'From' @TableValue 'Order by' @ OrderKey '' @order
Set @MAINSQL = 'SELECT TOP' CAST (@PageSize As Varchar (30)) '*'
'From (' @virtualtable ") as Tablea '
'Where' @IDENTKEY NOT IN (SELECT TOP '
Cast ((@ PAGENUMBER-1) * @Pagesize As Varchar (30)) '' @IDENTKEY
'From (' @virtualtable ') as tableb)
Exec (@mainsql)
End
Go
