分页查询 收藏
IF OBJECT_ID(N'dbo.p_show') IS NOT NULL DROP PROCEDURE dbo.p_show GO /**//*--实现分页的通用存储过程
显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询,其它情况使用临时表的方法 如果视图或查询结果中有主键,不推荐此方法 如果使用查询语句,而且查询语句使用了order by,则查询语句必须包含top 语句 最后更新时间: 2008.01.20
--邹建 2003.09(引用请保留此信息)--*/ /**//*--调用示例
EXEC dbo.p_show @QueryStr = N'tb', @PageSize = 5, @PageCurrent = 3, @FdShow = 'id, colid, name', @FdOrder = 'colid, name' select id, colid from tb order by colid, name EXEC dbo.p_show @QueryStr = N' SELECT TOP 100 PERCENT * FROM dbo.sysobjects ORDER BY xtype', @PageSize = 5, @PageCurrent = 2, @FdShow = 'name, xtype', @FdOrder = 'xtype, name' --*/ CREATE PROC dbo.p_show @QueryStr nvarchar(4000), -- 表名、视图名、查询语句 @PageSize int=10, -- 每页的大小(行数) @PageCurrent int=1, -- 要显示的页 @FdShow nvarchar (4000) = N'', -- 要显示的字段列表,如果查询结果不需要标识字段,需要指定此值,且不包含标识字段 @FdOrder nvarchar (1000) = N'' -- 排序字段列表 AS SET NOCOUNT ON DECLARE @FdName sysname, --表中的主键或表、临时表中的标识列名 @Id1 sysname, --开始和结束的记录号 @Id2 sysname, @Obj_ID int --对象ID --表中有复合主键的处理
DECLARE @strfd nvarchar(2000), --复合主键列表 @strjoin nvarchar(4000), --连接字段 @strwhere nvarchar(2000) --查询条件 SELECT @Obj_ID = OBJECT_ID(@QueryStr), @FdShow = CASE WHEN @FdShow > N'' THEN N' ' + @FdShow ELSE N' *' END, @FdOrder = CASE WHEN @FdOrder > N'' THEN N' ORDER BY ' + @FdOrder ELSE N' ' END, @QueryStr = CASE WHEN @Obj_ID IS NULL THEN N' (' + @QueryStr + N')A' ELSE N' ' + @QueryStr END -- 如果显示第一页,可以直接用 top 来完成
IF @PageCurrent = 1 BEGIN SELECT @Id1 = CAST(@PageSize as varchar(20)) EXEC(N' SELECT TOP ' + @Id1 + N' ' + @FdShow + N' FROM ' + @QueryStr + N' ' + @FdOrder ) RETURN END -- 如果是表, 则检查表中是否有标识更或主键
IF @Obj_ID IS NULL OR OBJECTPROPERTY(@Obj_ID, 'IsTable') = 0 GOTO lb_usetemp ELSE BEGIN SELECT @Id1 = CAST(@PageSize as varchar(20)), @Id2 = CAST((@PageCurrent - 1) * @PageSize as varchar(20)) -- 标识列
SELECT @FdName = name FROM dbo.syscolumns WHERE id = @Obj_ID AND status = 0x80 IF @@ROWCOUNT = 0 --如果表中无标识列,则检查表中是否有主键 BEGIN DECLARE @pk_number int SELECT
@strfd = N'', @strjoin = N'', @strwhere = N'' SELECT
@strfd = @strfd + N',' + QUOTENAME(name), @strjoin = @strjoin + N' AND A.' + QUOTENAME(name) + N'=B.' + QUOTENAME(name), @strwhere = @strwhere + N' AND B.' + QUOTENAME(name) + N' IS NULL' FROM( SELECT IX.id, IX.indid, IXC.colid, ixc.keyno, C.name FROM dbo.sysobjects O, dbo.sysindexes IX, dbo.sysindexkeys IXC, dbo.syscolumns C WHERE O.parent_obj = @Obj_ID AND O.xtype = 'PK' AND O.name = IX.name AND IX.id = @Obj_ID AND IX.id = IXC.id AND IX.indid = IXC.indid AND IXC.id = C.id AND IXC.colid = C.colid )A ORDER BY keyno SELECT
@pk_number = @@ROWCOUNT, @strfd = STUFF(@strfd, 1, 1, N''), @strjoin = STUFF(@strjoin, 1, 5, N''), @strwhere = STUFF(@strwhere, 1, 5, N'') IF @pk_number = 0
GOTO lb_usetemp --如果表中无主键,则用临时表处理 ELSE IF @pk_number = 1 BEGIN SELECT @FdName = @strfd GOTO lb_useidentity -- 使用单一主键 END ELSE GOTO lb_usepk -- 使用复合主键 END END /**//*--使用标识列或主键为单一字段的处理方法--*/
lb_useidentity: EXEC(N' SELECT TOP ' + @Id1 + N' ' + @FdShow + N' FROM '+@QueryStr + N' WHERE ' + @FdName + ' NOT IN( SELECT TOP ' + @Id2 + N' ' + @FdName + ' FROM ' + @QueryStr + N' ' + @FdOrder + N') ' + @FdOrder + N' ') RETURN /**//*--表中有复合主键的处理方法--*/
lb_usepk: EXEC(N' SELECT ' + @FdShow + N' FROM( SELECT TOP ' + @Id1 + N' A.* FROM ' + @QueryStr + N' A LEFT JOIN( SELECT TOP ' + @Id2 + N' ' + @strfd + N' FROM ' + @QueryStr + N' ' + @FdOrder + N' )B ON ' + @strjoin + N' WHERE ' + @strwhere + N' ' + @FdOrder + N' )A ' + @FdOrder + N' ') RETURN /**//*--用临时表处理的方法--*/
lb_usetemp: SELECT @FdName = QUOTENAME(N'ID_' + CAST(NEWID() as varchar(40))), @Id1 = CAST(@PageSize * (@PageCurrent-1) as varchar(20)), @Id2 = CAST(@PageSize * @PageCurrent-1 as varchar(20)) EXEC(N'
SELECT ' + @FdName + N' = IDENTITY(int, 0, 1), ' + @FdShow + N' INTO #tb FROM( SELECT TOP 100 PERCENT * FROM ' + @QueryStr + N' ' + @FdOrder + N' )A ' + @FdOrder + N' SELECT
' + @FdShow + N' FROM #tb WHERE ' + @FdName + ' BETWEEN ' + @Id1 + ' AND ' + @Id2 + N' ' ) GO 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/zjcxc/archive/2003/12/29/20080.aspx |
|
来自: Mike Lee > 《SQL Server》