Ms-sql Paging function +asp

CREATE PROCEDURE [dbo]. [Usp_getlist]

(

@pageIndex int = 1--current page 0 means return all conforming content

, @pageSize int = 10--Page size

, @SID nvarchar (30) = '--primary key field

, @strGetField nvarchar (1000) = ' * '--columns to be returned

, @strTableName nvarchar (30) = '--table name

, @strWhere nvarchar (2000) = '--Query criteria (note: Where to take in the condition)

, @strOrderBy nvarchar (300) = '--sort

--, @MemberID int=0--member ID

)

As

SET NOCOUNT on

DECLARE @strSQL nvarchar (4000)

DECLARE @startPos int

DECLARE @endPos int

DECLARE @num int

SET @startPos = @pageSize * (@pageIndex-1) +1

SET @endPos = @startPos + @pageSize-1

--Page Size * (pages-1)

SET @num = @pageSize * (@PageIndex-1)

IF @PageIndex!=0

BEGIN

IF @strWhere!= '

SET @strSQL = ' SELECT top ' + cast (@pageSize as nvarchar) + "+ @strGetField + ' from ' + @strTableName + ' WHERE (' + cast (@SID as nvarchar) + ' not in (SELECT top ' + cast (@num as nvarchar) + "+ cast (@SID as nvarchar) + ' from ' + @st Rtablename + ' where ' + @strWhere + ' ORDER BY ' + cast (@strOrderBy as nvarchar (255)) + ') "and ' + @strwhere + ' ORDER BY ' +cast (@ Strorderby as varchar (255)) + '

Else

SET @strSQL = ' SELECT top ' + cast (@pageSize as nvarchar) + "+ @strGetField + ' from ' + @strTableName + ' WHERE (' + cast (@SID as nvarchar) + ' not in (SELECT top ' + cast (@num as nvarchar) + "+ cast (@SID as nvarchar) + ' from ' + @st Rtablename + ' ORDER BY ' + cast (@strOrderBy as nvarchar (255)) + ") Order BY ' + cast (@strOrderBy as varchar (255)) + '

--set @strSQL = ' SELECT top page size * from users WHERE (ID. (SELECT Top (page size * (current page-1)) ID from the users order by ID DESC) ORDER by ID DESC '

End

ELSE

BEGIN

IF @strWhere!= '

SET @strSQL = ' SELECT ' + @strGetField + ' from ' + @strTableName + ' where ' + @strWhere + ' ORDER BY ' + @strOrderBy

ELSE

SET @strSQL = ' SELECT ' + @strGetField + ' from ' + @strTableName + ' ORDER BY ' + @strOrderBy

End

--print (@strSQL)

EXEC (@strSQL)

Go

Second stored procedure

CREATE PROCEDURE [dbo]. [Usp_gettotal]

(

@strTableName nvarchar (30) = ',

@strWhere nvarchar (2000) = '--Query criteria (note: Where to take in the condition)

)

As

SET NOCOUNT off

DECLARE @strSQL nvarchar (2500)

IF @strWhere!= '

SET @strSQL = ' SELECT count (*) as total from [' + @strTableName + '] where ' + @strWhere

ELSE

SET @strSQL = ' SELECT count (*) as total from [' + @strTableName + '] '

EXEC (@strSQL)

Go

Here are two functions that call this stored procedure. Can be placed in a containing file

, note that some of the variables used in the following function are global variables, not arguments passed in, so you need to call the function money and assign a value to the global variable.

<%

' Take the total number of records stored procedure

Public Function Getdatarowcount (strTableName, strwhere)

Dim maxcount

Dim myobj

Dim Rscount

Maxcount = 0

Set myobj = Server.CreateObject ("Adodb.command")

With MyObj

. ActiveConnection = conn

. CommandText = "Usp_gettotal"

. CommandType = 4

. Prepared = True

. Parameters.Append. CreateParameter ("@strTableName", 1, strTableName)

. Parameters.Append. CreateParameter ("@strWhere", 1, strwhere,)

Set Rscount =. Execute

End With

Set myobj = Nothing

Maxcount = Rscount ("Total")

RsCount.close:Set Rscount = Nothing

Getdatarowcount = Maxcount

End Function

' Single-table paging stored procedures

Sub Getdatars ()

Dim obj

Set obj = Server.CreateObject ("Adodb.command")

With obj

. ActiveConnection = conn

. CommandText = "Usp_getlist"

. CommandType = 4

. Prepared = True

. Parameters.Append. CreateParameter ("@pageIndex", 3, 1, 4, Ipageindex)

. Parameters.Append. CreateParameter ("@pageSize", 3, 1, 4, ipagesize)

. Parameters.Append. CreateParameter ("@SID", 1, SID) ' 2000

. Parameters.Append. CreateParameter ("@strGetField", 1, 1000, Strgetfield)

. Parameters.Append. CreateParameter ("@tableName", 1, strTableName)

. Parameters.Append. CreateParameter ("@strWhere", 1, strwhere,)

. Parameters.Append. CreateParameter ("@strOrderBy", 1, Strorderby)

Set rstobj =. Execute

End With

Set obj = Nothing

End Sub

%>

The following is an instance of calling this component page stored procedure

' ############ #定义分页存储过程所要使用的变量 ##################################################

' 1. Define variables, set initial values for partial variables

Dim Ipageindex, Ipagesize, Irowcount

Dim Strwherem, strTableName, SID, Strgetfield, strwhere, Strorderby

Number of record bars displayed in ipagesize = 12 ' page

strTableName = "dataTable" table name

SID = "id" PRIMARY Key Name

Strgetfield = "id, field1,field2,field3"

' Name of the field to display

strwhere = "Where1=1 and where2=2"

' WHERE clause

Strorderby = "id desc" ' Sort

' 2, execute Getpageinf () get (1) Ipageindex: Current page, (2) Irowcount: Total record number, (3) Ipagecount: Total pages

Call GetPageInfo () ' include_gb/page.asp

' 3, first define Rstobj, then execute Getdatars (). Sub Getdatars () uses the definition above to indicate, field, where, number of pages, total records, etc. to access the stored procedure and pay the result to Rstobj (set)

Dim rstobj

Call Getdatars ()

' 4,rstobj gets the return data, starts the loop output, writes to the process, facilitates the call

Sub Showproducts ()

If Rstobj.eof Then

Response. Write ("No Data Temporarily")

Else

Do as not rstobj.eof

Call Showprodtable (Rstobj ("id"), rstobj ("Newsname"), Rstobj ("Pictures"), Rstobj ("abstract"), Rstobj ("Addtime"))

Rstobj.movenext

Loop

End If

Closers (rstobj) ' Close link include_gb/connsitedata.asp

End Sub

'###############################################################