快捷導(dǎo)航

asp.net 分頁存儲(chǔ)過程實(shí)例剖析心得

更新時(shí)間：2011年10月18日 22:42:19 作者：

最近修改了個(gè)分頁存儲(chǔ)過程，作為菜鳥，還是從中獲益良多，這里就開始今天的分頁之旅了

1.開始還原下面先看一下原來的分頁存儲(chǔ)過程。

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

-- 創(chuàng)建臨時(shí)表 
-- 用來保存表的編號(hào)以及主鍵 
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 -- 取得總記錄數(shù) 

-- 判斷頁索引 
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 -- 刪除臨時(shí)表 
END 

從原分頁存儲(chǔ)過程很容易看出，這里運(yùn)用了臨時(shí)表保存編號(hào)，然后在通過pageIndex和pageSize計(jì)算所得，進(jìn)行分頁。

因?yàn)檫@里還以主鍵作為查詢條件，故臨時(shí)表中也保存了主鍵值。

很顯然，這里的臨時(shí)表無法做到通用，因?yàn)橹麈I的類型不一定是上面定義的INT型，也可以是其它的類型，比如：uniqueidentifier(全球唯一標(biāo)識(shí))。

這樣的話，這個(gè)存儲(chǔ)過程就碰到了問題，所以必須進(jìn)行改進(jìn)。

2.思路一
思路一很簡單，那就把這個(gè)類型聲明成一個(gè)變量，然后通過系統(tǒng)表獲取表的主鍵類型，再賦給變量不就可以了嗎?？雌饋砗苊烂睿环猎囈辉囅?。

我們可以插入下面一段代碼：

復(fù)制代碼代碼如下:

 
DECLARE @colType NVARCHAR(50) --主鍵列類型 
DECLARE @strSQL3 NVARCHAR(500) --創(chuàng)建臨時(shí)表語句 

SET @colType = (SELECT typ.name as 數(shù)據(jù)類型 
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) 

打印結(jié)果：

復(fù)制代碼代碼如下:

 
CREATE TABLE #Temp_Paging 
( 
Temp_Paging_Id uniqueidentifier, 
RowNumber INT 
) 

很顯然我們得到了所需要的臨時(shí)表。這時(shí)我很開心，因?yàn)榈玫搅宋蚁胍臇|西。但似乎還沒有結(jié)束，我要執(zhí)行下該存儲(chǔ)過程。

壞結(jié)果往往在這時(shí)出現(xiàn)：

這里就出現(xiàn)奇怪的事了，按打印出來的明明是正確的創(chuàng)建臨時(shí)表語句，而且也執(zhí)行了，為什么接下來對(duì)臨時(shí)表的操作又是無效的了？

找資料問同事，終于明白，原來臨時(shí)表分本地臨時(shí)表和全局臨時(shí)表。本地臨時(shí)表需要注意實(shí)際刪除的時(shí)間。

這里說得簡單一點(diǎn)：當(dāng)在用EXEC(@strSQL3) 創(chuàng)建臨時(shí)表的時(shí)候，同時(shí)已經(jīng)刪除了臨時(shí)表。因?yàn)镋XEC這個(gè)過程的會(huì)話已經(jīng)結(jié)束，臨時(shí)表就被刪除了。

這里有一篇博文做了更具體的解釋，大家可以參考：點(diǎn)滴在心頭_SQL臨時(shí)表

3.思路二

上面的思路似乎行不通，那不如再換個(gè)思路，干脆不要建立臨時(shí)表，用Select * from (select * from table) as temptable 這種思路代替創(chuàng)建臨時(shí)表。

代碼如下：

復(fù)制代碼代碼如下:

 
ALTER PROCEDURE [dbo].[sp_Sql_Paging] 
( 
@SqlDataTable NVARCHAR(4000), -- 表名 
@PrimaryKey NVARCHAR(4000), -- 主鍵名稱 
@Fields NVARCHAR(4000), -- 要返回的字段 
@pageSize INT, -- 頁尺寸 
@pageIndex INT, -- 頁碼 
@recordCount INT OUTPUT, -- 記錄總數(shù) 
@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 

--獲取總記錄數(shù) 
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 