Paging stored procedure: create procedure getRecordfromPage @tblname varchar (255), - Table name @fldname varchar (255), - field name @Pagesize INT = 10, - Page size @PageIndex INT = 1, - Typographic @iscount Bit = 0, - Return to the total number of records, non-0 values return @ORDERTYPE bit = 0, - Set the sort type, non-0 value, descending @Strwhere varchar (1000) = '- Query Conditions (Note: Don't add WHERE) AS
Declare @strsql varchar (1000) - Proficiency Declare @strtmp varchar (300) - Temporary Variable Declare @strORDER VARCHAR (400) - Sort Type
if @ORDERTYPE! = 0BEGIN SET @STRTMP = '<(Select Min' Set @strorder = 'Order By [' @fldName '] Desc'EndelseBegin 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 ')' @strorderif @pageIndex = 1begin set @strtmp = '' f @Strwhere! = '' set @strtmp = 'Where (' @Strwhere ') ')'
Set @strsql = 'SELECT TOP' STR (@PageSize) '* from [' @tblname ']' @strtmp '' @strorderend
If @iscount! = 0 set @strsql = 'select count (*) as total from [' @tblname ']'
--Print @STRSQLEXEC (@strsql)
