Create Procedure GetRecordfromPage @tblname VARCHAR (255), - Table Name @fldname varchar (255), - Field Name @Pagesize INT = 10, - Page Dimension @PageIndex INT = 1, - Type @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, descending @Strwhere varchar (1000) = '- Query Conditions (Note: Do not 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
