/*
Function name: GetRecordFromPage
Function function: Get data from a specified page
Parameter description: @tblName Table name containing data
@fldName Key field name
@PageSize Number of records per page
@PageIndex Page number to get
@OrderType Sort type, 0 - ascending, 1 - descending
@strWhere Query Conditions (Note: Do not add where)
Author: Iron Fist
Email: sunjianhua_kki@
Creation time: 2006-07-04
Modification time: 2006-07-04
*/
CREATE PROCEDURE GetRecordFromPage
@tblName varchar(255), -- table name
@fldName varchar(255), -- field name
@PageSize int = 10, -- Page size
@PageIndex int = 1, -- Page number
@OrderType bit = 0, -- Set the sort type, and if the value is not 0, descending order
@strWhere varchar(2000) = '' -- Query conditions (Note: do not add where)
AS
declare @strSQL varchar(6000) -- Subject statement
declare @strTmp varchar(1000) -- Temporary variable
declare @strOrder varchar(500) -- sort type
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName + '] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
exec (@strSQL)
GO