asp.net 分頁預存程序執行個體剖析心得

1.開始還原 下面先看一下原來的分頁預存程序。 複製代碼 代碼如下:ALTER PROCEDURE [dbo].[sp_Sql_Paging]
(
@SqlDataTable NVARCHAR(4000), -- 表名
@PrimaryKey NVARCHAR(4000), -- 主鍵名稱
@Fields NVARCHAR(4000), -- 要返回的欄位
@pageSize INT, -- 頁尺寸
@pageIndex INT, -- 頁碼
@recordCount INT OUTPUT, -- 記錄總數
@strOrderBy NVARCHAR(4000), -- 排序
@strWhere NVARCHAR(4000) -- 查詢條件
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @strSQL1 NVARCHAR(4000) -- SQL語句1
DECLARE @strSQL2 NVARCHAR(4000) -- SQL語句2

-- 建立暫存資料表
-- 用來儲存表的編號以及主鍵
CREATE TABLE #Temp_Paging
(
Temp_Paging_Id INT,
RowNumber INT
)
SET @strSQL1 = 'INSERT INTO [#Temp_Paging](Temp_Paging_Id, RowNumber) SELECT ' + @PrimaryKey + ', ROW_NUMBER() OVER (' + @strOrderBy + ') AS RowNumber FROM ' + @SqlDataTable + ' ' + @strWhere
EXEC SP_EXECUTESQL @strSQL1
SET @recordCount = @@ROWCOUNT -- 取得總記錄數

-- 判斷頁索引
IF @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize
BEGIN
SET @pageIndex = 1
END

-- 分頁查詢
SET @strSQL2 = 'SELECT ' + @Fields + ' FROM ' + @SqlDataTable + ' WHERE ' + @PrimaryKey + ' IN (SELECT Temp_Paging_Id FROM [#Temp_Paging] WHERE RowNumber BETWEEN ' + Str((@pageIndex - 1) * @pageSize + 1) + ' AND ' + Str(@pageIndex * @pageSize) + ') ' + @strOrderBy
EXEC SP_EXECUTESQL @strSQL2
DROP TABLE #Temp_Paging -- 刪除暫存資料表
END

從原分頁預存程序很容易看出，這裡運用了暫存資料表儲存編號，然後在通過pageIndex和pageSize計算所得，進行分頁。

因為這裡還以主鍵作為查詢條件，故暫存資料表中也儲存了主索引值。

很顯然，這裡的暫存資料表無法做到通用，因為主鍵的類型不一定是上面定義的INT型，也可以是其它的類型，比如：uniqueidentifier(全球唯一標識)。

這樣的話，這個預存程序就碰到了問題，所以必須進行改進。

2.思路一
思路一很簡單，那就把這個型別宣告成一個變數，然後通過系統資料表擷取表的主鍵類型，再賦給變數不就可以了嗎。看起來很美妙，不妨試一試先。

我們可以插入下面一段代碼： 複製代碼 代碼如下:DECLARE @colType NVARCHAR(50) --主鍵列類型
DECLARE @strSQL3 NVARCHAR(500) --建立暫存資料表語句

SET @colType = (SELECT typ.name as 資料類型
FROM sys.columns col
left join sys.types typ
on (col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id)
WHERE col.object_id = (SELECT object_id FROM sys.tables WHERE name = @SqlDataTable)
and exists
( SELECT 1 FROM sys.indexes idx
join sys.index_columns idxCol
on (idx.object_id = idxCol.object_id)
WHERE idx.object_id = col.object_id
AND idxCol.index_column_id = col.column_id
AND idx.is_primary_key = 1
))

SET @strSQL3 = 'CREATE TABLE #Temp_Paging
(
Temp_Paging_Id '+ @colType+',
RowNumber INT
)'
PRINT @strSQL3
--EXEC(@strSQL3)

列印結果： 複製代碼 代碼如下:CREATE TABLE #Temp_Paging
(
Temp_Paging_Id uniqueidentifier,
RowNumber INT
)

很顯然我們得到了所需要的暫存資料表。這時我很開心，因為得到了我想要的東西。但似乎還沒有結束，我要執行下該預存程序。

壞結果往往在這時出現：
這裡就出現奇怪的事了，按列印出來的明明是正確的建立暫存資料表語句，而且也執行了，為什麼接下來對暫存資料表的操作又是無效的了？

找資料問同事，終於明白，原來暫存資料表分本地暫存資料表和全域暫存資料表。本地暫存資料表需要注意實際刪除的時間。

這裡說得簡單一點：當在用EXEC(@strSQL3) 建立暫存資料表的時候，同時已經刪除了暫存資料表。因為EXEC這個過程的會話已經結束，暫存資料表就被刪除了。

這裡有一篇博文做了更具體的解釋，大家可以參考：點滴在心頭_SQL暫存資料表

3.思路二

上面的思路似乎行不通，那不如再換個思路，乾脆不要建立暫存資料表，用Select * from (select * from table) as temptable 這種思路代替建立暫存資料表。

代碼如下： 複製代碼 代碼如下:ALTER PROCEDURE [dbo].[sp_Sql_Paging]
(
@SqlDataTable NVARCHAR(4000), -- 表名
@PrimaryKey NVARCHAR(4000), -- 主鍵名稱
@Fields NVARCHAR(4000), -- 要返回的欄位
@pageSize INT, -- 頁尺寸
@pageIndex INT, -- 頁碼
@recordCount INT OUTPUT, -- 記錄總數
@strOrderBy NVARCHAR(4000), -- 排序
@strWhere NVARCHAR(4000) -- 查詢條件
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @strSQL1 NVARCHAR(4000) -- SQL語句1
DECLARE @strSQL2 NVARCHAR(4000) -- SQL語句2
DECLARE @strSQL3 NVARCHAR(4000) -- SQL語句3

SET @strSQL1 = 'SELECT ' + @PrimaryKey + ', ROW_NUMBER() OVER (' + @strOrderBy + ') AS RowNumber FROM ' + @SqlDataTable + ' ' + @strWhere

--擷取總記錄數
SET @strSQL3 = 'SELECT @recordCount = COUNT(*) FROM ' + @SqlDataTable + ' ' + @strWhere
EXEC SP_EXECUTESQL
@stmt = @strSQL3,
@params = N'@recordCount AS INT OUTPUT',
@recordCount = @recordCount OUTPUT

--分頁查詢
IF @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize
BEGIN
SET @pageIndex = 1
END
SET @strSQL2 = 'SELECT ' + @Fields + ' FROM ' + @SqlDataTable + ' WHERE ' + @PrimaryKey + ' IN (SELECT '+@PrimaryKey+' FROM ('+@strSQL1+') TempTable WHERE RowNumber BETWEEN ' + Str((@pageIndex - 1) * @pageSize + 1) + ' AND ' + Str(@pageIndex * @pageSize) + ') ' + @strOrderBy
EXEC SP_EXECUTESQL @strSQL2
END

這裡有個小知識點，注意EXEC SP_EXECUTESQL的寫法以及和EXEC(@strsql)的區別。大家可以去找資料瞭解下。

關於分頁的一些事就寫到這了，僅供參考。