关注我
现在把基于SQL语句的分页使用存储过程实现，实现代码如下：
两种方案：
USE [MyBookShop]
GO
/****** 对象: StoredProcedure [dbo].[usp_ShowPage] 脚本日期: 09/19/2010 11:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: sms
-- Create date: 2010.9.12
-- Description: 分页，用到了ROW_NUMBER()
-- =============================================
CREATE PROCEDURE [dbo].[usp_ShowPage]
-- Add the parameters for the stored procedure here
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列，默认*
@strOrder varchar(255)='', -- 排序的字段名，必填
@strOrderType varchar(10)='ASC', -- 排序的方式,默认ASC
@PageSize int = 10, -- 页尺寸,默认
@PageIndex int = 1, -- 页码,默认
@strWhere varchar(1500) = '' -- 查询条件(注意: 不要加where)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @strSQL varchar(5000)
if @strWhere !=''
set @strWhere=' where '+@strWhere
set @strSQL=
'SELECT * FROM ('+
'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS pos,'+@strGetFields+' '+
'FROM ['+@tblName+'] '+@strWhere+
') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
exec (@strSQL)
END
另一种写法：
USE [MyBookShop]
GO
/****** 对象: StoredProcedure [dbo].[usp_SelectRecordByPage] 脚本日期: 09/19/2010 11:52:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: sms
-- Create date: 2010.9.12
-- Description: 从MyBookShop数据库中的Books表中选择数据
-- 传入选择的页数，每页显示数固定为条
-- =============================================
CREATE PROCEDURE [dbo].[usp_SelectRecordByPage]
@page int, -- 要选择第X页的数据
@pageSize int=10 -- 每页显示数，默认为条
AS
BEGIN
declare @sql nvarchar(1000)
set @sql='select top '+convert(varchar(10),@pageSize)+' * from Books where id not in
(select top '+convert(varchar(20),(@page-1)*@pageSize)+' id from Books order by id) order by id'
exec (@sql)
END
关于ASP.NET分页先写到这里......