- Get the data of the specified page Create Procedure getRecordfromPage @tblname varchar (255), - Table name @fldname varchar (255), - field name @PageSize INT = 10, - Page size @PageIndex int = 1, - Page code @iscount bit = 0, - Return to the total number of records, non-0 values returns to @ORDERTYPE BIT = 0, - Set the sort type, non-0 value is descended @Strwhere varchar (1000) = '- Query Conditions (attention : Don't add where) AS
Declare @strsql varchar (6000) - Professional Sentences Declare @STRTMP VARCHAR (100) - 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 [" @fldname "] 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 =" "if @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 "]"
EXEC (@strsql)
Go
