sql存儲過程分頁

　　CREATE PROC myx_prPageRecordset
    @queryStr nvarchar()
    @keyField nvarchar ()
    @pageSize int
    @pageNumber int
    AS
    BEGIN
    DECLARE @sqlText AS nvarchar()
    DECLARE @sqlTable AS nvarchar()
    SET @sqlTable = SELECT TOP + CAST((@pageNumber + ) * @pageSize AS varchar()) + + @queryStr
    SET @sqlText =
    SELECT TOP + CAST(@pageSize AS varchar()) + * +
    FROM ( + @sqlTable + ) AS tableA +
    WHERE + @keyField + NOT IN(SELECT TOP +
    CAST(@pageNumber * @pageSize AS varchar()) + + @keyField +
    FROM ( + @sqlTable + ) AS tableB)
    EXEC (@sqlText)
    END

　　GO

　　核心代碼
    Dim strsql As String
    myComm = New SqlClientSqlCommand(myx_prPageRecordset myConn)
    myCommCommandType = CommandTypeStoredProcedure
    myCommParametersAdd(New SqlClientSqlParameter(@queryStr SqlDbTypeNVarChar ))
    myCommParameters(@queryStr)Value = * from tbpage order by id DESC
    myCommParametersAdd(New SqlClientSqlParameter(@keyField SqlDbTypeNVarChar ))
    myCommParameters(@keyField)Value = [id]
    myCommParametersAdd(New SqlClientSqlParameter(@pageSize SqlDbTypeNVarChar ))
    myCommParameters(@pageSize)Value = PageSize
    myCommParametersAdd(New SqlClientSqlParameter(@pageNumber SqlDbTypeNVarChar ))
    myCommParameters(@pageNumber)Value = myPage

　　呵呵執行幾W條的代碼只需毫秒左右

　　建立一個test(idnamefid)

　　向test添充幾十條數據使id=(即遞增的integer)其他任意在Tsql Debugger給改存儲過程分別傳遞如下參數:

　　@queryStr= * from test
    @keyField=[ID]
    @pageSize=
    @pageNumber=

　　問題出來了看輸出結果(注意id):
    id name fid
    kwklover
    kwklover
    kwklover

　　根據傳入參數我們的預期應該是:
    id name fid
    kwklover
    kwklover
    kwklover

　　下面是我參照小春的存儲分頁寫的分頁存儲過程可以解決上面的問題:
    CREATE Procedure prGetRecordByPage
    (
    @PageSize int 每頁的記錄條數
    @PageNumber int 當前頁面
    @QuerySql varchar()部分查詢字符串如* From Test order by id desc
    @KeyField varchar()
    )
    AS
    Begin

　　Declare @SqlTable AS varchar()
    Declare @SqlText AS Varchar()

　　Set @SqlTable=Select Top +CAST(@PageNumber*@PageSize AS varchar())+ +@QuerySql
    Set @SqlText=Select Top +Cast(@PageSize AS varchar())+ * From
    +(+@SqlTable+) As TembTbA
    +Where +@KeyField+ Not In (Select Top +CAST((@PageNumber)*@PageSize AS varchar())+ +@KeyField+ From
    +(+@SqlTable+) AS TempTbB)
    Exec(@SqlText)

　　End
    GO

From:http://tw.wingwit.com/Article/program/Oracle/201311/17708.html