MSsql數據庫如何實現分頁存儲的代碼實例

　　reate PROCEDURE Sp_Conn_Sort
(
@tblName   varchar()       表名

@strGetFields varchar() = *  需要返回的列

@fldName varchar()=      排序的字段名

@PageSize   int =           頁尺寸

@PageIndex  int =            頁碼

@doCount  bit =    返回記錄總數 非 值則返回

@OrderType bit =   設置排序類型 非 值則降序
@strWhere  varchar()=  查詢條件 (注意: 不要加 where)
)
AS

declare @strSQL   varchar()       主語句

declare @strTmp   varchar()        臨時變量

declare @strOrder varchar()        排序類型

if @doCount !=

  begin

    if @strWhere !=

    set @strSQL = select count(*) as Total from + @tblName + where +@strWhere

    else

    set @strSQL = select count(*) as Total from + @tblName

end

以上代碼的意思是如果@doCount傳遞過來的不是就執行總數統計以下的所有代碼都是@doCount為的情況

else

begin

if @OrderType !=

begin

    set @strTmp = <(select min

set @strOrder = order by + @fldName + desc

如果@OrderType不是就執行降序這句很重要！

end

else

begin

    set @strTmp = >(select max

    set @strOrder = order by + @fldName + asc

end

if @PageIndex =

begin

    if @strWhere !=

    set @strSQL = select top + str(@PageSize) + +@strGetFields+   from + @tblName + where + @strWhere + + @strOrder

     else

     set @strSQL = select top + str(@PageSize) + +@strGetFields+   from + @tblName + + @strOrder

如果是第一頁就執行以上代碼這樣會加快執行速度

end

else

begin

以下代碼賦予了@strSQL以真正執行的SQL代碼

set @strSQL = select top + str(@PageSize) + +@strGetFields+   from

    + @tblName + where + @fldName + + @strTmp + (+ @fldName + ) from (select top + str((@PageIndex)*@PageSize) + + @fldName + from + @tblName + + @strOrder + ) as tblTmp)+ @strOrder

if @strWhere !=

    set @strSQL = select top + str(@PageSize) + +@strGetFields+   from

        + @tblName + where + @fldName + + @strTmp + (

        + @fldName + ) from (select top + str((@PageIndex)*@PageSize) +

        + @fldName + from + @tblName + where + @strWhere +

        + @strOrder + ) as tblTmp) and + @strWhere + + @strOrder

end

end

exec (@strSQL)



sql server 單主鍵高效分頁存儲過程 (支持多字段排序)

Create PROC P_viewPage       
    /**//*        
    nzperfect [no_mIss] 高效通用分頁存儲過程(雙向檢索)   QQ:        
    敬告適用於單一主鍵或存在唯一值列的表或視圖        
    ps教程:Sql語句為字節調用時請注意傳入參數及sql總長度不要超過指定范圍        
    */        
    @TableName VARCHAR()     表名        
    @FieldList VARCHAR()    顯示列名如果是全部字段則為*        
    @PrimaryKey VARCHAR()    單一主鍵或唯一值鍵        
    @Where VARCHAR()        查詢條件 不含where字符如id> and len(userid)>        
    @Order VARCHAR()        排序 不含order by字符如id ascuserid desc必須指定asc或desc        
    注意當@SortType=時生效記住一定要在最後加上主鍵否則會讓你比較郁悶        
    @SortType INT               排序規則 :正序asc :倒序desc :多列排序方法        
    @RecorderCount INT          記錄總數 :會返回總記錄        
    @PageSize INT               每頁輸出的記錄數        
    @PageIndex INT              當前頁數        
    @TotalCount INT OUTPUT       記返回總記錄        
    @TotalPageCount INT OUTPUT   返回總頁數        
AS        
SET NOCOUNT ON        
    IF ISNULL(@TotalCount) = SET @TotalCount =         
    SET @Order = RTRIM(LTRIM(@Order))        
    SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))        
    SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)) )        
    WHILE CHARINDEX( @Order) > or CHARINDEX( @Order) >         
        BEGIN        
            SET @Order = REPLACE(@Order )        
            SET @Order = REPLACE(@Order )        
        END        
    IF ISNULL(@TableName) = or ISNULL(@FieldList) =         
            or ISNULL(@PrimaryKey) =         
            or @SortType < or @SortType >        
            or @RecorderCount  < or @PageSize < or @PageIndex <         
        BEGIN        
            PRINT(ERR_)        
            RETURN        
        END        
    IF @SortType =         
        BEGIN        
            IF (UPPER(RIGHT(@Order))!= ASC AND UPPER(RIGHT(@Order))!= DESC)        
                BEGIN PRINT(ERR_) RETURN END        
        END        
    DECLARE @new_where VARCHAR()        
    DECLARE @new_where VARCHAR()        
    DECLARE @new_order VARCHAR()        
    DECLARE @new_order VARCHAR()        
    DECLARE @new_order VARCHAR()        
    DECLARE @Sql VARCHAR()        
    DECLARE @SqlCount NVARCHAR()        
    IF ISNULL(@where) =         
        BEGIN        
            SET @new_where =         
            SET @new_where = Where          
        END        
    ELSE        
        BEGIN        
            SET @new_where = Where + @where        
            SET @new_where = Where + @where + AND         
        END        
    IF ISNULL(@order) = or @SortType =   or @SortType =         
        BEGIN        
            IF @SortType =         
                BEGIN        
                    SET @new_order = orDER BY + @PrimaryKey + ASC        
                    SET @new_order = orDER BY + @PrimaryKey + DESC        
                END        
            IF @SortType =         
                BEGIN        
                    SET @new_order = orDER BY + @PrimaryKey + DESC        
                    SET @new_order = orDER BY + @PrimaryKey + ASC        
                END        
        END        
    ELSE        
        BEGIN        
            SET @new_order = orDER BY + @Order        
        END        
  
    IF @SortType = AND  CHARINDEX(+@PrimaryKey+ +@Order)>        
    BEGIN        
        SET @new_order = orDER BY + @Order        
        SET @new_order = @Order +         
        SET @new_order = REPLACE(REPLACE(@new_orderASC{ASC})DESC{DESC})        
        SET @new_order = REPLACE(REPLACE(@new_order{ASC}DESC){DESC}ASC)        
        SET @new_order = orDER BY + SUBSTRING(@new_orderLEN(@new_order))        
        IF @FieldList <> *        
            BEGIN        
                SET @new_order = REPLACE(REPLACE(@Order + ASC)DESC)        
                SET @FieldList = + @FieldList        
                WHILE CHARINDEX(@new_order)>        
                    BEGIN        
                        IF CHARINDEX(SUBSTRING(+@new_orderCHARINDEX(@new_order))+@FieldList+)>        
                            BEGIN        
                                SET @FieldList =        
                                @FieldList + + SUBSTRING(@new_orderCHARINDEX(@new_order))        
                            END        
                        SET @new_order =        
                        SUBSTRING(@new_orderCHARINDEX(@new_order)+LEN(@new_order))        
                    END        
                SET @FieldList = SUBSTRING(@FieldListLEN(@FieldList))        
            END        
        END   
       
    SET @SqlCount = Select @TotalCount=COUNT(*)@TotalPageCount=CEILING((COUNT(*)+)/        
    + CAST(@PageSize AS VARCHAR)+) FROM (Select * FROM + @TableName + @new_where+) AS T        
    IF @RecorderCount  =         
        BEGIN        
            EXEC SP_EXECUTESQL @SqlCountN@TotalCount INT OUTPUT@TotalPageCount INT OUTPUT        
            @TotalCount OUTPUT@TotalPageCount OUTPUT        
        END        
    ELSE        
        BEGIN        
            Select @TotalCount = @RecorderCount      
        END        
    IF @PageIndex > CEILING((@TotalCount+)/@PageSize)        
        BEGIN        
            SET @PageIndex =  CEILING((@TotalCount+)/@PageSize)        
        END        
    IF @PageIndex = or @PageIndex >= CEILING((@TotalCount+)/@PageSize)        
        BEGIN        
            IF @PageIndex = 返回第一頁數據        
                BEGIN        
                    SET @Sql = Select * FROM (Select TOP + STR(@PageSize) + + @FieldList + FROM         
                    + @TableName + @new_where + @new_order +) AS TMP + @new_order  
                END        
            IF @PageIndex >= CEILING((@TotalCount+)/@PageSize)  返回最後一頁數據        
                BEGIN        
                    SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (        
                    + Select TOP + STR(ABS(@PageSize*@PageIndex@TotalCount@PageSize))        
                    + + @FieldList + FROM         
                    + @TableName + @new_where + @new_order + ) AS TMP         
                    + @new_order        
                END        
        END        
    ELSE    
          
        BEGIN        
        IF @SortType = wwwpptcom 僅主鍵正序排序        
            BEGIN        
                IF @PageIndex <= CEILING((@TotalCount+)/@PageSize)/  正向檢索        
                    BEGIN        
                        SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM         
                        + @TableName + @new_where + @PrimaryKey + >         
                        + (Select MAX( + @PrimaryKey + ) FROM (Select TOP         
                        + STR(@PageSize*(@PageIndex)) + + @PrimaryKey        
                        + FROM + @TableName        
                        + @new_where + @new_order + ) AS TMP) + @new_order        
                    END        
                ELSE  反向檢索        
                    BEGIN        
                        SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (        
                        + Select TOP + STR(@PageSize) +         
                        + @FieldList + FROM         
                        + @TableName + @new_where + @PrimaryKey + <         
                        + (Select MIN( + @PrimaryKey + ) FROM (Select TOP        
                        + STR(@TotalCount@PageSize*@PageIndex) + + @PrimaryKey        
                        + FROM + @TableName        
                        + @new_where + @new_order + ) AS TMP) + @new_order        
                        + ) AS TMP + @new_order        
                    END        
            END        
        IF @SortType =   僅主鍵反序排序        
            BEGIN        
                IF @PageIndex <= CEILING((@TotalCount+)/@PageSize)/  正向檢索        
                    BEGIN        
                        SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM         
                        + @TableName + @new_where + @PrimaryKey + <         
                        + (Select MIN( + @PrimaryKey + ) FROM (Select TOP         
                        + STR(@PageSize*(@PageIndex)) + + @PrimaryKey        
                        + FROM + @TableName        
                        + @new_where + @new_order + ) AS TMP) + @new_order        
                    END        
                ELSE  反向檢索        
                    BEGIN        
                        SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (        
                        + Select TOP + STR(@PageSize) +         
                        + @FieldList + FROM         
                        + @TableName + @new_where + @PrimaryKey + >         
                        + (Select MAX( + @PrimaryKey + ) FROM (Select TOP         
                        + STR(@TotalCount@PageSize*@PageIndex) + + @PrimaryKey        
                        + FROM + @TableName        
                        + @new_where + @new_order + ) AS TMP) + @new_order        
                        + ) AS TMP + @new_order        
                    END        
            END        
        IF @SortType =   多列排序必須包含主鍵且放置最後否則不處理        
            BEGIN        
                IF CHARINDEX( + @PrimaryKey + + @Order) =         
                    BEGIN PRINT(ERR_) RETURN END        
                    IF @PageIndex <= CEILING((@TotalCount+)/@PageSize)/  正向檢索        
                        BEGIN        
                            SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (         
                            + Select TOP + STR(@PageSize) + + @FieldList + FROM (         
                            + Select TOP + STR(@PageSize*@PageIndex) + + @FieldList        
                            + FROM + @TableName + @new_where + @new_order + ) AS TMP         
                            + @new_order + ) AS TMP + @new_order        
                        END        
                    ELSE  反向檢索        
                        BEGIN        
                            SET @Sql = Select TOP + STR(@PageSize) + + @FieldList + FROM (         
                            + Select TOP + STR(@PageSize) + + @FieldList + FROM (         
                            + Select TOP + STR(@TotalCount@PageSize *@PageIndex+@PageSize) + + @FieldList        
                            + FROM + @TableName + @new_where + @new_order + ) AS TMP         
                            + @new_order + ) AS TMP + @new_order        
                        END        
            END        
        END        
    PRINT(@SQL)        
    EXEC(@Sql)

From:http://tw.wingwit.com/Article/program/MySQL/201311/29564.html