淺談SQL Server所實現建立分頁功能的預存程序的分析及代碼

在這裡我們能看到一種使用sql預存程序寫的資料控制項分頁功能，不管給開發工具中的那個資料控制項，都只要調用這個預存程序就可以實現這樣的方法了，簡單吧，我們再也不用在各個頁面都寫很多的SQL語句了。Misrosoft  SQL Server是微軟開發的一種管理資料的資料庫，現在世面上面最流行的幾款資料庫是Access,SQL Server, MySQl，Oracl資料庫，所謂這些資料庫，只要你學會一個資料庫的文法等，其他的你都就大致會使用了，現在就我學習的SQL Server我在這裡大致寫寫， 我們已經學習了一年了，我們學到了什麼呢？我自我問了一下，我發現我真的什麼都沒有學到。除了寫個建立表啊，執行簡單的Sql語句，寫簡單的預存程序，我們寫過函數嗎？寫過SQL的一些進階的東西嗎？就是簡單的預存程序，都是我們自己隨手寫出來的嗎，還要查資料，要是真正自己寫呢，我就不能說了？？？？？？？？？，所以我們學習的東西還有很多呢，我們要堅持，堅持就是勝利。

下面就是支援資料分頁的預存程序，值得研究一下，當然，我們要認真的看，才能從中學到好多東西哦。

建立預存程序：

--擷取分頁的總共資料的資訊

create procedure p_PageList

(

@TableName nvarchar(300),  --你所要分頁的表名

@PKey nvarchar(50),        --主鍵 預設ID

@FieldList nvarchar(500),  --需要搜尋的欄位

@Condition nvarchar(1000), --條件，搜尋什麼條件

@OrderBy nvarchar(250),    --排序Order By ID

@Sql nvarchar(1000),       --可以使用,也可以不使用,程式可以自動產生sql語句，當然，那樣只能簡單的查出來所需要的資訊

@SqlGetRC nvarchar(1000),  --得到總的sql語句，也可以不使用，也可以指定

@CurrPage int,             --當前你所查看的那一頁

@PageSize int,             --每頁所要現實的數目

@RecordCount int,          --擷取資料庫中資料的總數目，可以傳，也可以不傳

@result int output         --輸出參數

)

as

declare @PageCount int

if @SqlGetRC=''  --如果總的資料sql語句為空白

Set @SqlGetRC='Select @RecordCount=COUNT(0) FROM '+@TableName+@Condition

if @RecordCount=-1  --

begin

exec sp_executesql @SqlGetRC,N'@RecordCount int out',@RecordCount out

end

Set @PageCount=(@RecordCount+@PageSize-1)/@PageSize

if @CurrPage>@PageCount AND @PageCount>0

Set @CurrPage=@PageCount

 

if @Sql=''

begin

    if @PageSize=0

       set @PageSize=10

    if @CurrPage=1

       Set @Sql='select top '+Cast(@PageSize as nvarchar)+' '+@FieldList+' FROM '+@TableName+@Condition+' '+@OrderBy

    else

       Set @Sql='select top '+Cast(@PageSize as nvarchar)+' '+@FieldList+' FROM '+@TableName+' WHERE ' +@Pkey+' NOT IN (SELECT TOP '+Cast((@CurrPage-1)*@PageSize as nvarchar)+' '+@Pkey+' FROM '++ ' ' + @Condition + ' ' + @OrderBy + ') ' + replace(@Condition,' WHERE 1=1',' ') + ' ' + @OrderBy

end

exec(@Sql)

Select RecordCount=@RecordCount,PageCount=@PageCount

Set  @Result=1

知識點總結：

1：sp_executesql  執行可以多次重複使用或動態產生的 Transact-SQL 陳述式或批處理。Transact-SQL 陳述式或批處理可以包含嵌入參數。

備忘：在批處理、名稱範圍和資料庫上下文方面，sp_executesql 與 EXECUTE 的行為相同。sp_executesql stmt 參數中的 Transact-SQL 陳述式或批處理在執行 sp_executesql 語句時才編譯。隨後，將編譯 stmt 中的內容，並將其作為執行計畫運行。該執行計畫獨立於名為 sp_executesql 的批處理的執行計畫。sp_executesql 批處理不能引用調用 sp_executesql 的批處理中聲明的變數。sp_executesql 批處理中的本地遊標或變數對調用 sp_executesql 的批處理是不可見的。對資料庫上下文所作的更改只在 sp_executesql 語句結束前有效。

如果只更改了語句中的參數值，則 sp_executesql 可用來代替預存程序多次執行 Transact-SQL 陳述式。因為 Transact-SQL 陳述式本身保持不變，僅參數值發生變化，所以 SQL Server 查詢最佳化工具可能重複使用首次執行時所產生的執行計畫。

2：Cast  將一種資料類型的運算式顯式轉換為另一種資料類型的運算式。CAST 和 CONVERT 提供相似的功能。

3:replace

將第一個字串運算式中第二個給定字串運算式的所有執行個體都替換為第三個運算式。

文法

REPLACE ( 'string_expression1' , 'string_expression2' ,'string_expression3' )

參數

' string_expression1 '

要搜尋的字串運算式。string_expression1 參數的資料類型可以是可隱式轉換為 nvarchar 或 ntext 的資料類型。

' string_expression2 '

嘗試尋找的字串運算式。string_expression2 參數的資料類型可以是可隱式轉換為 nvarchar 或 ntext 的資料類型。

' string_expression3 '

替換字串運算式。string_expression3 參數的資料類型可以是可隱式轉換為 nvarchar 或 ntext 的資料類型。

傳回值

nvarchar 或 ntext

如果任意參數為 NULL，則返回 NULL。

樣本

下面的樣本在產品名稱列表中搜尋名稱“Anton”並將其替換為“Anthony”：

	複製代碼
SELECT REPLACE(ProductName, 'Anton', 'Anthony')FROM Products