This is the stored procedure after I have written ------------------------------------------------------------------------------------------------------------------------------ -----------------
Set quoted_identifier off goset ANSI_NULLS ON GO
/ * Author: Guo new line features: Paging through data Description: This process functions to implement paging of data, input parameters @ SelectFieldName, @ tbName, @ strWhere, @OrderFieldName together as a complete Select query statement, this statement can not be Contains an intermediate table * / alter procedure usp_getrecordfromPage @tblname varchar (1000), - Table name @selectfieldname varchar (4000), the field name to display (do not add select) @Strwhere varchar (4000), - Query Conditions ( Note: Don't add where) @ORDERFIELDNAME VARCHAR (255), - Sort Class Terminal name @pagesize int, - Page size @PageIndex INT = 1, - Total number @ORDERTYPE BIT = Page size @PageCount Int Output 0 - Set the sort type, non-0 value, descending asce
Declare @strsql varchar (4000) - Proficiency Declare @strtmp Varchar (4000) - Temporary Variable Declare @strORDER VARCHAR (1000) - Sort Type DECLARE @STRROUNT NVARCHAR (4000) - The statement used to query the total number of records
Set @ OrderfieldName = LTRIM (RTRIM (@ORDERFIELDNAME)) SET @selectfieldname = Ltrim (RTRIM (@selectfieldname) set @strwhere = ltrim (Rtrim (@strwhere))
IF @ selectfieldname = '' begin set @ selectfieldname = '*' end
If @ORDERTYPE! = 0BEGIN SET @STRTMP = '<(select min' set @strorder = 'order by' @ORDERFIELDNAME 'Desc'endelsebegin set @STRTMP ='> (select max 'set @strorder =' Order By ' @ORDERFIELDNAME 'ASC'END
IF @strWhere! = '' BEGIN SET @strSQL = 'select top' ltrim (rtrim (str (@PageSize))) '' @ SelectFieldName 'from' @tblName 'where' @OrderFieldName @strTmp '(' Right (@ORderfieldName, Len (@ORDERFIELDNAME) -Charindex ('.', @ OrderfieldName) ') from (SELECT TOP' LTRIM (RTR ((@ PageIndex-1) * @ PageSize ))) '' @StrordName 'WHERE' @Strwhere '' @strorder ') as tbltmp) and' @Strwhere ' @strordeelsebegin set @strsql = 'select top' ltrim (rtrim (str (@PageSize))) '' @ SelectFieldName 'from' @tblName 'where' @OrderFieldName @strTmp '(' right (@ OrderFieldName, len ( @ORDERFIELDNAME) -Charindex ('.', @ OrderfieldName)) ') from (SELECT TOP' LTRIM (RTR))) '' @Orderfieldname 'from ' @TBLNAME @StrOR ') as tbltmp) ' @strorderend
IF @PageIndex = 1begin set @strtmp = '' if @Strwhere! = '' Begin set @strtmp = 'Where' @Strwhere end set @strsql = 'SELECT TOP' LTRIM (rtrim (str (@pagesize))) '' @ SelectFieldName 'from' @tblName @strTmp '' @strOrderENDPRINT (@strSQL) EXEC (@strSQL) IF @strWhere! = '' BEGIN SET @strRowCount = 'select @ iRowCount = count (* ) from ' @ TBLNAME ' Where ' @ StrwhereEndelsebegin set @strrowcount =' select @ irowcount = count (*) from ' @tblnameeend
EXEC SP_EXECUTESQL @ Strowcount, N '@ irowcount int out', @ irowcount out
Goset quoted_identifier off goset ANSI_NULLS ON Go
