method one:*********************************************** *******
Declare @sqlstr nvarchar (4000), - Query string @PageCount Int, - NAP @Pagesize Int, - per page @rowcount int search @ PageCount = 3, @ PageSize = 100 set @ SQLSTR = 'SELECT * from sysobjects'set nocount ondeclare @ P1 int - P1 is cursor idexec sp_cursoropen @ P1 output, @ sqlstr, @ scrollopt = 1, @ ccopt = 1, @ rowcount = @ rowcount OUTPUT select @rowcount as number of rows, ceiling ( 1.0 * @ rowcount / @ pagesize) as the number of pages, @ pagecount as this page set @pagecount = (@ pagecount-1) * @ pagesize 1 exec sp_cursorfetch @ P1,16, @ pagecount, @ pagesize exec sp_cursorclose @ P1
Method 2 ******************************************************************* *
- Sql Query Analyzer performed at the script stored procedure to establish p_splitpagecreate procedure p_splitpage if exists (select * from dbo.sysobjects where id = object_id (N '[dbo] [p_splitpage].') And OBJECTPROPERTY (id, N'IsProcedure ') = 1) Drop Procedure [DBO]. [P_splitpage] Go
- Using SQL unprecedented stored procedure to achieve paging create procedure p_splitpage @sql nvarchar (4000), - the SQL statement @currentpage int = 2 to be executed, - the page number to display @PageSize Int = 10, - per page Size @PageCount Int = 0 out - Total number Asset NoCount ONDECLARE @ P1 INT
EXEC SP_CURSoropen @ p1 output, @ SQL, @ scrollopt = 1, @ ccopt = 1, @ rowcount = @ PageCount Output
select @ pagecount = ceiling (1.0 * @ pagecount / @ pagesize), @ currentpage = (@ currentpage-1) * @ pagesize 1select @currentpageexec sp_cursorfetch @ p1,16, @ currentpage, @ pagesize exec sp_cursorclose @ p1go
- Then you can call in the program, for details, please refer to the script exec p_splitpage 'Select Top 1000 ID, Name from Sysobjects', 2 Method 3 ************ ****************************************
Create Proc Page @PAGENUM INT As set NoCount on / * ----- This sentence is very important :)), otherwise it will only recognize insert #change ... this data set :)) * / declare @ SQL NVARCHAR (500) - Declaration Dynamic SQL Execution Statement Declare @PageCount Int - Current Page
- Total number of records to get the current database declare @Row_num int start @ row_num = count (*) from cat_list
- Create a temporary table as data filter Create Table #change (t_id int)
- Judgment the current page number if @Row_num> 6 - Big than the page display record number, page begin set @ row_num = @ Pagenum * 6
IF @ row_num = 6 Select Top 6 * from cat_list else begin set @row_num = (@ PAGENUM-1) * 6 set @ PageCount = @ row_num set @ SQL = N'Insert #change (t_id) SELECT TOP ' CAST (@ pagecount as char (100)) 't_id from cat_list where t_id not in (select t_id from #change)' exec sp_executesql @sql select top 6 * from cat_list where t_id not in (select t_id from #change) end end else - Reality All Data Select * from cat_list end
The client calls:
<% DIM T_COM DIM T_RS'DECLARE DATAMANAGE RECORDSET
dim parameters set T_com = server.createobject ( "adodb.command") T_com.ActiveConnection = conn T_com.CommandText = "page" T_com.CommandType = adCmdStoredProc 'T_com.Prepared = true set parameters = T_com.CreateParameter ( "@ pagenum", Adinteger, Adparaminput) T_com.Parameters.Append Parameters Dim Page Page = Request.QueryString ("Page") If Page = "" "= 1 end if t_com (" @ PAGENUM ") = Page Set T_RS = T_com.execute
