SQL2000分頁儲存過程，針對表,2005有自帶的row_number

iSQlServer發表於2009-07-16
SQL儲存過程

注：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)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-609340/，如需轉載，請註明出處，否則將追究法律責任。

相關文章