I can't remember where the paging process you see is, I feel very good, I will say that the introduction is high. . ^ _ ^, Unfortunately, I can't get record sets and return value at the same time. I made a little improvement, huh, I feel good, but there is a shortcoming, here will mention: - Get data for the specified page
Create Procedure Pagination3
@tblname varchar (255), - Table name
@StrGetfields varchar (1000) = '*', - the column that needs to be returned
@fldname varchar (255) = '', - Sort field name
@PageSize Int = 10, - Page Size
@PageIndex INT = 1, - Page
@docount bit = 0, - Return to the total number of records, non-0 values return
@ORDERTYPE bit = 0, - Set the sort type, descending order of non-0 value
@Strwhere Varchar (1500) = '' - Query Conditions (Note: Don't add WHERE)
AS
Declare @strsql varchar (5000) - main statement
Declare @strtmp varchar (110) - Temporary variable
Declare @strorder varchar (400) - Sort Type
IF @docount! = 0
Begin
IF @strwhere! = ''
Set @strsql = "Select count (*) as total from [" @TBLNAME "] where" @ strwhere
Else
Set @strsql = "Select count (*) as total from [" @TBLNAME "]"
End - The above code means that if @Docount passes is not 0, the total number of statistics is performed. All of the following code is @docount is 0
Else
Begin
IF @ORDERTYPE! = 0
Begin
Set @strtmp = "<(SELECT MIN"
Set @strorder = "Order by [" @fldname "] desc"
- If @ORDERTYPE is not 0, it will be designed, this sentence is very important!
end
Else
Begin
Set @strtmp = "> (SELECT MAX"
Set @strorder = "Order by [" @fldname "] ASC"
end
IF @PageIndex = 1
Begin
IF @strwhere! = ''
Set @strsql = "SELECT TOP" STR (@Pagesize) "" @ strGetfields "from [" @TBLNAME "] where" @strwhere "" @strordeRelse
Set @STRSQL = "SELECT TOP" STR (@Pagesize) " @ strGetfields " from [" @tblname "] " @strorder
- If you are the first page, you will implement the above code, which will speed up the execution speed.
end
Else
Begin
- The following code gives @strsql to real execute SQL code
Set @strsql = "SELECT TOP" STR (@PageSize) " @ strGetfields " 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) " @ strGetfields " from ["
@TBLNAME "] Where [" @fldname "]" @strtmp "(["
@fldname "]) from (SELECT TOP" STR ((@ PageIndex-1) * @ PageSize) "["
@fldname "] from [" @tblname "] where" @strwhere ""
@strorder ") as tbltmp) and" @strwhere "" @strorder
end
end
EXEC (@strsql)
Go
The above is the code of the original stored procedure. . Here is the changed code
Alter Procedure Proc_getRecordfromPage @ TBLName Varchar (255), - Table Name
@StrGetfields Varchar (1000), - Requires Returned Colum @fldname Varchar (255), Sort Field Name @PageSize Int, - Page Size @pageIndex Int, - Total Number of Records Non 0 value returns to @ORDERTYPE TINYINT, - Set the sort type, non-0 value is descended @Strwhere varchar (1500), - Query Conditions (Note: Don't add WHERE)
@Recordcount Int Output --get Total Recordcount, Ericsun 2004.11.24 Add
AS
Declare @strsql nvarchar (4000) - main statement
Declare @strtmp varchar (110) - Temporary variable
Declare @strorder varchar (400) - Sort Type
IF @docount! = 0
Begin
IF @strwhere! = ''
Set @strsql = 'select count (*) as total from [' @TBLNAME '] Where' @ Strwhere Else
Set @strsql = 'select count (*) as total from [' @TBLNAME ']'
end
- The above code means that if @Docount passes is not 0, the total number of statistics is performed. All of the following code is @docount is 0
Else
Begin
IF @ORDERTYPE! = 0 Begin set @strtmp = '<(select min' set @strorder = 'order by [' @fldname '] desc' - If @ORDERTYPE is not 0, it will be designed, this sentence is very important ! ELSE BEGIN SET @STRTMP = '> (Select Max' Set @strorder = 'Order By [' @fldname '] ASC' END
----- Get Total Recordcount, Ericsun 2004.11.24 Add ---- if @Strwhere! = '' begin set @ strsql = 'select count (*) as total INTO ## tmptable from [' @ tblname '] where ' ' ' @strWhere exec sp_executesql @strSQL select @ RecordCount = Total from ## tmpTable drop table ## tmpTable end else beginset @ strSQL =' select count (*) as Total into ## tmpTable from [ ' @ tblName ']' EXEC SP_EXECUTESQL @STRSQL SELECT @ recordcount = Total from ## TMPTABLE DROP TABLE ## TMPTABLE END ---------------------------------------------------------------------------- ----------------------------
IF @PageIndex = 1 - If it is the first page, execute the above code, this will speed up the speed begin if @Strwhere! = '' Begin
Set @STRSQL = 'SELECT TOP' STR (@PageSize) '' @ strGetfields 'from [' @TBLNAME '] Where' @Strwhere ' @strorder Else Begin Set @strsql =' SELECT Top ' str (@Pagesize) ' ' @ strGetfields ' from [' @tblname '] ' @strorder end end else begin - The following code gives @strsql to real execution SQL code set @strsql = 'SELECT TOP' STR (@PageSize) '' @ strGetfields '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) '' @ strGetfields 'from [' @TBLNAME '] where [' @fldname ']' @STRTMP '([' @fldname ']) from (SELECT TOP' STR ((@ PageIndex-1) * @ PageSize) '[' @fldname '] from [' @tblname ' ] Where ' @StrWhere ' ' @StrORDER ') AS TBLTMP) and ' @ Strwhere '' @strorder end end
Exec sp_executesql @strsql --sp_executesql's default permissions is that there is no meaning of the total number of records on the Pubic. Unfortunately, the changed part is to execute once every time you turn it, it is every page. Re-reading the total number of records, it is really failure, I have never thought of solving the solution ... .
