学步园

注：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('表名或对象名不存在当前数据库中，请检查', 16, 1)
    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, 1) OR C.name=index_col(@TableName, I.indid, 2) OR
            C.name=index_col(@TableName, I.indid, 3) OR C.name=index_col(@TableName, I.indid, 4) OR
            C.name=index_col(@TableName, I.indid, 5) OR C.name=index_col(@TableName, I.indid, 6) OR
            C.name=index_col(@TableName, I.indid, 7) OR C.name=index_col(@TableName, I.indid, 8) OR
               C.name=index_col(@TableName, I.indid, 9) OR C.name=index_col(@TableName, I.indid, 10) OR
            C.name=index_col(@TableName, I.indid, 11) OR C.name=index_col(@TableName, I.indid, 12) OR
            C.name=index_col(@TableName, I.indid, 13) OR C.name=index_col(@TableName, I.indid, 14) OR
            C.name=index_col(@TableName, I.indid, 15) OR C.name=index_col(@TableName, I.indid, 16)
        )
OPEN cur
IF @@CURSOR_ROWS<=0
BEGIN
    RAISERROR('指定表中不存在主键，请检查', 16, 1)
    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)

--TablePage 'TableName','',39,17