邹建的 sql server 分页存储过程 以备用
来源:互联网 发布:c4d mac 百度云 编辑:程序博客网 时间:2024/04/29 18:43
- IF OBJECT_ID(N'dbo.pagination') IS NOT NULL
- DROP PROCEDURE dbo.pagination
- GO
- CREATE PROC dbo.pagination
- @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