ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL2000分页存储过程,针对表,2005有自带的row_number

SQL2000分页存储过程,针对表,2005有自带的row_number

原创 Linux操作系统 作者:iSQlServer 时间:2009-07-16 15:22:17 0 删除 编辑

注:INSERT INTO SELECT 在已有identity列时会报错,以下存储过程直接读取结构,所以不会

CREATE PROCEDURE TablePage
(
    
@TableName    nvarchar(255),        --表名
    @OrderStr    nvarchar(500),        --排序字段
    @PageNumber    int,                --当前页
    @PageSize    int                    --每页记录数
)
AS
DECLARE @sqlstr        nvarchar(4000)
DECLARE @sqlins        nvarchar(1500)    --导入数据至临时表时用SQL
DECLARE @sqljoin    nvarchar(2000)    --连接显示数据用SQL
DECLARE @sqlpage    nvarchar(200)    --分页计算
DECLARE @TableId    int                --表ID
DECLARE @IndexId    int                --主键约束ID
DECLARE @RowCount    int                --表中数据条数
DECLARE @BegNumber    int                --起始记录
DECLARE @EndNumber    int                --截止记录
DECLARE @MaxPage    int                --最大页

DECLARE @KeyName    sysname            --主键字段名
DECLARE @KeyType    sysname            --主键字段类型
DECLARE @KeyPrec    smallint        --主键栏位为变长时长度
DECLARE @KeyScale    tinyint            --主键栏位精度

IF @PageNumber<=0
    
SET @PageNumber=1
IF @PageSize<=0
    
SET @PageSize=1

SET @TableId = OBJECT_ID(@TableName)
IF @TableId IS NULL
BEGIN
    
RAISERROR('表名或对象名不存在当前数据库中,请检查'161)
    
RETURN
END

--取得总记录数
SET @sqlstr='SELECT @iRowCount=COUNT(*) FROM '+@TableName
EXEC SP_EXECUTESQL @sqlstr, N'@iRowCount int OUTPUT'@RowCount OUTPUT

--取得主键
DECLARE cur CURSOR STATIC FOR
    
SELECT    C.name KeyName, T.name KeyType, C.prec, C.scale
    
FROM    syscolumns C
    
LEFT JOIN sysindexes I ON I.id=C.id
    
LEFT JOIN systypes T ON T.xtype=C.xtype
    
WHERE    C.id=@TableId
        
AND (I.status & 0x800)=0x800
        
AND (
            C.name
=index_col(@TableName, I.indid, 1OR C.name=index_col(@TableName, I.indid, 2OR
            C.name
=index_col(@TableName, I.indid, 3OR C.name=index_col(@TableName, I.indid, 4OR
            C.name
=index_col(@TableName, I.indid, 5OR C.name=index_col(@TableName, I.indid, 6OR
            C.name
=index_col(@TableName, I.indid, 7OR C.name=index_col(@TableName, I.indid, 8OR
               C.name
=index_col(@TableName, I.indid, 9OR C.name=index_col(@TableName, I.indid, 10OR
            C.name
=index_col(@TableName, I.indid, 11OR C.name=index_col(@TableName, I.indid, 12OR
            C.name
=index_col(@TableName, I.indid, 13OR C.name=index_col(@TableName, I.indid, 14OR
            C.name
=index_col(@TableName, I.indid, 15OR C.name=index_col(@TableName, I.indid, 16)
        )
OPEN cur
IF @@CURSOR_ROWS<=0
BEGIN
    
RAISERROR('指定表中不存在主键,请检查'161)
    
RETURN
END
--存在主键,创建同样主键临时表
FETCH next FROM cur INTO @KeyName@KeyType@KeyPrec@KeyScale
SET @sqlstr='CREATE TABLE #TMP(ROW_NUMBER INT IDENTITY(1, 1)'
SET @sqlins=''
SET @sqljoin=''
WHILE@@FETCH_STATUS=0 )
BEGIN
    
--插入SQL
    IF @sqlins<>''
        
SET @sqlins=@sqlins+','
    
SET @sqlins=@sqlins+@KeyName
    
    
--连接SQL
    IF @sqljoin<>''
        
SET @sqljoin=@sqljoin+' AND #TMP.'+@KeyName+'='+@TableName+'.'+@KeyName
    
ELSE
        
SET @sqljoin=@sqljoin+' LEFT JOIN '+@TableName+' ON #TMP.'+@KeyName+'='+@TableName+'.'+@KeyName
    
SET @sqlstr=@sqlstr+','+@KeyName+' '+@KeyType

    
--定义主键类型及长度
    IF @KeyType='binary' OR @KeyType='char' OR @KeyType='nchar' OR @KeyType='nvarchar' 
        
OR @KeyType='varbinary' OR @KeyType='varchar'
    
BEGIN
        
--类型是可变长度,无精度时,取prec栏位
        --不定长,无精度的包括: binary(173), char(175), nchar(239),nvarchar(231), varbinary(165), varchar(167)
        SET @sqlstr=@sqlstr+'('+CAST(@KeyPrec AS nvarchar)+')'
    
END
    
ELSE IF @KeyType='decimal' OR @KeyType='numeric'
    
BEGIN
        
--类型是可变长度且拥有精度时,取prec栏位+','+scale栏位
        --不定长,有精度的包括: decimal(106), numeric(108)
        SET @sqlstr=@sqlstr+'('+CAST(@KeyPrec AS nvarchar)+','+CAST(@KeyScale AS nvarchar)+')'
    
END
    
--其它类型是定长类型时,无须指定长度

    
FETCH next FROM cur INTO @KeyName@KeyType@KeyPrec@KeyScale
END
CLOSE cur
DEALLOCATE cur
SET @sqlstr=@sqlstr+')'

--插入SQL
SET @sqlins='INSERT INTO #TMP('+@sqlins+') SELECT '+@sqlins+' FROM '+@TableName

--计算页面
SET @MaxPage=CAST(@RowCount/@PageSize AS INT)
IF @RowCount%@PageSize>0
    
SET @MaxPage=@MaxPage+1
IF @MaxPage=0
    
SET @MaxPage=1
IF @PageNumber>@MaxPage
    
SET @PageNumber=@MaxPage

SET @BegNumber=(@PageNumber-1)*@PageSize+1
SET @EndNumber=@PageNumber*@PageSize
SET @sqlpage=' WHERE #TMP.ROW_NUMBER BETWEEN '+CAST(@BegNumber AS nvarchar)+' AND '+CAST(@EndNumber AS nvarchar)

--连接SQL
SET @sqljoin='SELECT #TMP.ROW_NUMBER,'+@TableName+'.* FROM #TMP '+@sqljoin

--整合SQL
SET @sqlstr=@sqlstr+' '+@sqlins+' '+@sqljoin+@sqlpage

--加上删除命令
SET @sqlstr=@sqlstr+' DROP TABLE #TMP'
EXEC(@sqlstr)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-609340/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    1319
  • 访问量
    2074179