分享
 
 
 

MS SQL Server 2005 开发之分页存储过程

王朝mssql·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

在工作中经常会用到分页显示数据,如果使用.NET开发,可以使用DataGrid控件的分页功能,也可以使用PagedDataSource类来辅助完成。但这两种方式都将从数据库中获取所有的记录,将记录数目较大时,效率很低,因此直接使用存储过程来进行分页,则将会提供更好的性能。在CSDN的数据库开发社区,有许多大侠都写出了自己的分页存储过程,经过整理之后,给一个完整的例子。

首先创建一个分页存储过程,下面的分页存储过程是在pbsql大侠提供的分页存储过程的基础上修改而成的。

CREATE PROCEDURE sp_page

@strTable varchar(50), --表名

@strColumn varchar(50), --按该列来进行分页

@intColType int, --@strColumn列的类型,0-数字类型,1-字符类型,2-日期时间类型

@intOrder bit, --排序,0-顺序,1-倒序

@strColumnlist varchar(800), --要查询出的字段列表,*表示全部字段

@intPageSize int, --每页记录数

@intPageNum int, --指定页

@strWhere varchar(800), --查询条件

@intPageCount int OUTPUT --总页数

AS

DECLARE @sql nvarchar(4000) --用于构造SQL语句

DECLARE @where1 varchar(800) --构造条件语句

DECLARE @where2 varchar(800) --构造条件语句

IF @strWhere is null or rtrim(@strWhere)=''

-- 为了避免SQL关键字与字段、表名等连在一起,首先为传入的变量添加空格

BEGIN --没有查询条件

SET @where1=' WHERE '

SET @where2=' '

END

ELSE

BEGIN --有查询条件

SET @where1=' WHERE ('+@strWhere+') AND '

SET @where2=' WHERE ('+@strWhere+') '

END

set @strColumn = ' ' + @strColumn + ' '

set @strColumnlist = ' ' + @strColumnlist + ' '

--构造SQL语句,计算总页数。计算公式为 总页数 = Ceiling ( 记录个数 / 页大小 ) http://www.knowsky.com

SET @sql='SELECT @intPageCount=CEILING((COUNT(*)+0.0)/'

+ CAST(@intPageSize AS varchar)

+ ') FROM ' + @strTable + @where2

--执行SQL语句,计算总页数,并将其放入@intPageCount变量中

EXEC sp_executesql @sql,N'@intPageCount int OUTPUT',@intPageCount OUTPUT

--将总页数放到查询返回记录集的第一个字段前,此语句可省略

SET @strColumnlist= Cast(@intPageCount as varchar(30)) + ' as PageCount,' + @strColumnlist

IF @intOrder=0 --构造升序的SQL

SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar) +

@strColumnlist +

' FROM ' + @strTable + @where1 +

@strColumn + '>(SELECT MAX('+@strColumn+') '+

' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) +

@strColumn + ' FROM '+ @strTable+@where2+'ORDER BY '+@strColumn+') t) ORDER BY '+ @strColumn

ELSE --构造降序的SQL

SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar) +

@strColumnlist+

' FROM '+ @strTable + @where1 +

@strColumn + '<(SELECT MIN('+@strColumn+') '+

' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) +

@strColumn + ' FROM '+ @strTable+@where2+'ORDER BY '+@strColumn+' DESC) t) ORDER BY '+

@strColumn + ' DESC'

IF @intPageNum=1--第一页

SET @sql='SELECT TOP '+CAST(@intPageSize AS varchar) + @strColumnlist + ' FROM '+@strTable+

@where2+'ORDER BY '+@strColumn+CASE @intOrder WHEN 0 THEN '' ELSE ' DESC'

END

--PRINT @sql

EXEC(@sql)

GO

下面创建一个数据表,进行测试,并向表中添加123个测试数据。

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[myUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[myUser]

GO

CREATE TABLE [dbo].[myUser] (

[UserId] [int] IDENTITY (1, 1) NOT NULL ,

[UserName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

GO

declare @i int

set @i=1

while ( @i <= 123 )

begin

insert into myUser (UserName) values ( 'test' + cast(@i as varchar(6)))

set @i = @i + 1

end

调用存储过程

declare @o int

exec sp_page 'myUser','UserId',0,0,'*',15,1,'',@o output

exec sp_page 'myUser','UserId',0,0,'*',15,9,'',@o output

如果在.Net中,可以使用输出参数。下面给出C#的从存储过程中输出参数的例子

......

int iPage=1;

SqlCommand cmd = new SqlCommand("sp_page", conn );

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@strTable", "myUser");

cmd.Parameters.Add("@strColumn", "UserId");

cmd.Parameters.Add("@intColType", 0);

cmd.Parameters.Add("@intOrder", 0);

cmd.Parameters.Add("@strColumnlist", "*");

cmd.Parameters.Add("@intPageSize", 15);

cmd.Parameters.Add("@intPageNum", iPage);

cmd.Parameters.Add("@strWhere", "");

SqlParameter paramPageCount = cmd.Parameters.Add("@intPageCount", SqlDbType.Int);

paramPageCount.Direction = ParameterDirection.Output;

//可以改为ExecuteReader()

cmd.ExecuteNonQuery();

Response.Write(paramPageCount.Value.ToString());

http://blog.csdn.net/mengyao/archive/2007/02/16/1511276.aspx

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有