fclzly 2007-11-23 09:31
再来一个分页的存储过程,这可比别的好多了,就是不知速度如何!
存储过程:
ALTER PROCEDURE spPagination
@FieldList Nvarchar(200),--字段列表
@TableName Nvarchar(20), --表名
@WhereStr Nvarchar(500),--条件语句(须写完整,如"where Name='sea' and image=0",如果使用OR语句,须用():如:"Where (Name='sea' OR image=0)"
@PrimaryKey Nvarchar(20),--主键
@SortStr Nvarchar(100),--排序语句(须写完整,如"Order By ID,Nname")
@SortStrDesc Nvarchar(100), --倒序语句(须写完整,如"Order By ID desc,Nname desc")
@PageSize int,--页记录数
@PageNo int,--页码
@RecordCount int OUTPUT,--返回记录总数
@PageCount int OUTPUT--返回页总数
AS
/*定义局部变量*/
declare @intBeginID nvarchar(20)
declare @intEndID nvarchar(20)
declare @intRecordCount int
declare @intRowCount int
declare @TmpSelect NVarchar(600)
/*关闭计数*/
set nocount on
/*
set @PageNo=7
set @PageSize=2
set @SortStr='order by subproclassid, ProductID'
set @SortStrDesc='order by subproclassid desc, ProductID desc'
*/
/*求总记录数*/
Set @TmpSelect = 'set nocount on;select @SPintRootRecordCount = count(*) from ' @TableName ' ' @WhereStr
execute sp_executesql
@TmpSelect,
N'@SPintRootRecordCount int OUTPUT',
@SPintRootRecordCount=@intRecordCount OUTPUT
/*返回总记录数*/
set @RecordCount = @intRecordCount
if @intRecordCount=0
--没有记录则返回一个空记录集
Begin
Set @TmpSelect='Select ' @FieldList ' from ' @TableName ' ' @WhereStr
Execute sp_executesql @TmpSelect
set @RecordCount=0
set @PageCount=1
End
else
--有记录则返回记录集
begin
/*返回总页数*/
if @intRecordCount <> 0
begin
set @PageCount=floor((@intRecordCount 1.0-1.0) / @PageSize)
if @PageCount<(@intRecordCount 1.0-1.0) / @PageSize
set @PageCount=@PageCount 1
end
else
set @PageCount=0
/*判断页码是否正确
如果页码小于1,设置页码为1,如果页码大于总页数,设置页码为总页数*/
if @PageNo<1
set @PageNo=1
else
if @PageNo>@PageCount
set @PageNo=@PageCount
/*求结束记录位置*/
set @intRowCount = @PageNo * @PageSize
/*如果是最后页则返回余下的记录*/
if @PageNo=@PageCount
set @PageSize=@RecordCount - (@PageNo-1) * @PageSize
/* 开始分页 */
set @TmpSelect= 'select * from ' @TableName ' where ' @PrimaryKey ' = any ('
set @TmpSelect=@TmpSelect 'select top ' str(@PageSize) ' ' @PrimaryKey ' from ' @TableName ' where ' @PrimaryKey ' in (select top ' str(@intRowCount) ' ' @PrimaryKey ' from ' @TableName
set @TmpSelect=@TmpSelect ' ' @WhereStr ' ' @SortStr ') ' @SortStrDesc
set @TmpSelect=@TmpSelect ') ' @SortStr
execute sp_executesql @TmpSelect
end
/*返回受上一行影响的行数*/
return @@rowcount
VB类:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Configuration
Namespace Gyone.DataAccess
Public Class Pagination
** connStr As String = ConfigurationSettings.AppSettings("connStr")
** dsCommand As New SqlDataAdapter()
'------------------------------------------------------------------------------------------------
** _FieldList As String = "*"
** _TableName As String
** _WhereStr As String = ""
** _PrimaryKey As String
** _SortStr As String = ""
** _SortStrDesc As String
** _PageSize As Integer = 15
** _PageNo As Integer = 1
** _RecordCount As Integer
** _PageCount As Integer
'----------