建站
咨詢
售后
建站咨詢
新聞中心
最近修改了個(gè)分頁(yè)存儲(chǔ)過(guò)程，作為菜鳥(niǎo)，還是從中獲益良多，這里就開(kāi)始今天的分頁(yè)之旅了。
肅南裕固族自治ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書(shū)未來(lái)市場(chǎng)廣闊！成為成都創(chuàng)新互聯(lián)公司的ssl證書(shū)銷售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：028-86922220（備注：SSL證書(shū)合作）期待與您的合作！
1.開(kāi)始還原
下面先看一下原來(lái)的分頁(yè)存儲(chǔ)過(guò)程。
- ALTER PROCEDURE [dbo].[sp_Sql_Paging]
- (
- @SqlDataTable NVARCHAR(4000), -- 表名
- @PrimaryKey NVARCHAR(4000), -- 主鍵名稱
- @Fields NVARCHAR(4000), -- 要返回的字段
- @pageSize INT, -- 頁(yè)尺寸
- @pageIndex INT, -- 頁(yè)碼
- @recordCount INT OUTPUT, -- 記錄總數(shù)
- @strOrderBy NVARCHAR(4000), -- 排序
- @strWhere NVARCHAR(4000) -- 查詢條件
- )
- AS
- BEGIN
- SET NOCOUNT ON
- DECLARE @strSQL1 NVARCHAR(4000) -- SQL語(yǔ)句1
- DECLARE @strSQL2 NVARCHAR(4000) -- SQL語(yǔ)句2
- -- 創(chuàng)建臨時(shí)表
- -- 用來(lái)保存表的編號(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ù)
- -- 判斷頁(yè)索引
- IF @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize
- BEGIN
- SET @pageIndex = 1
- END
- -- 分頁(yè)查詢
- 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
從原分頁(yè)存儲(chǔ)過(guò)程很容易看出，這里運(yùn)用了臨時(shí)表保存編號(hào)，然后在通過(guò)pageIndex和pageSize計(jì)算所得，進(jìn)行分頁(yè)。
因?yàn)檫@里還以主鍵作為查詢條件，故臨時(shí)表中也保存了主鍵值。
很顯然，這里的臨時(shí)表無(wú)法做到通用，因?yàn)橹麈I的類型不一定是上面定義的INT型，也可以是其它的類型，比如：uniqueidentifier(全球唯一標(biāo)識(shí))。
這樣的話，這個(gè)存儲(chǔ)過(guò)程就碰到了問(wèn)題，所以必須進(jìn)行改進(jìn)。
2.思路一
思路一很簡(jiǎn)單，那就把這個(gè)類型聲明成一個(gè)變量，然后通過(guò)系統(tǒng)表獲取表的主鍵類型，再賦給變量不就可以了嗎。看起來(lái)很美妙，不妨試一試先。
我們可以插入下面一段代碼：
- DECLARE @colType NVARCHAR(50) --主鍵列類型
- DECLARE @strSQL3 NVARCHAR(500) --創(chuàng)建臨時(shí)表語(yǔ)句
- 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é)果：
- CREATE TABLE #Temp_Paging
- (
- Temp_Paging_Id uniqueidentifier,
- RowNumber INT
- )
很顯然我們得到了所需要的臨時(shí)表。這時(shí)我很開(kāi)心，因?yàn)榈玫搅宋蚁胍臇|西。但似乎還沒(méi)有結(jié)束，我要執(zhí)行下該存儲(chǔ)過(guò)程。
壞結(jié)果往往在這時(shí)出現(xiàn)：
這里就出現(xiàn)奇怪的事了，按打印出來(lái)的明明是正確的創(chuàng)建臨時(shí)表語(yǔ)句，而且也執(zhí)行了，為什么接下來(lái)對(duì)臨時(shí)表的操作又是無(wú)效的了？
找資料問(wèn)同事，終于明白，原來(lái)臨時(shí)表分本地臨時(shí)表和全局臨時(shí)表。本地臨時(shí)表需要注意實(shí)際刪除的時(shí)間。
這里說(shuō)得簡(jiǎn)單一點(diǎn)：當(dāng)在用EXEC(@strSQL3) 創(chuàng)建臨時(shí)表的時(shí)候，同時(shí)已經(jīng)刪除了臨時(shí)表。因?yàn)镋XEC這個(gè)過(guò)程的會(huì)話已經(jīng)結(jié)束，臨時(shí)表就被刪除了。
這里有一篇博文做了更具體的解釋，大家可以參考：點(diǎn)滴在心頭_SQL臨時(shí)表
#p#
3.思路二
上面的思路似乎行不通，那不如再換個(gè)思路，干脆不要建立臨時(shí)表，用Select * from (select * from table) as temptable 這種思路代替創(chuàng)建臨時(shí)表。
代碼如下：
- ALTER PROCEDURE [dbo].[sp_Sql_Paging]
- (
- @SqlDataTable NVARCHAR(4000), -- 表名
- @PrimaryKey NVARCHAR(4000), -- 主鍵名稱
- @Fields NVARCHAR(4000), -- 要返回的字段
- @pageSize INT, -- 頁(yè)尺寸
- @pageIndex INT, -- 頁(yè)碼
- @recordCount INT OUTPUT, -- 記錄總數(shù)
- @strOrderBy NVARCHAR(4000), -- 排序
- @strWhere NVARCHAR(4000) -- 查詢條件
- )
- AS
- BEGIN
- SET NOCOUNT ON
- DECLARE @strSQL1 NVARCHAR(4000) -- SQL語(yǔ)句1
- DECLARE @strSQL2 NVARCHAR(4000) -- SQL語(yǔ)句2
- DECLARE @strSQL3 NVARCHAR(4000) -- SQL語(yǔ)句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
- --分頁(yè)查詢
- 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
這里有個(gè)小知識(shí)點(diǎn)，注意EXEC SP_EXECUTESQL的寫(xiě)法以及和EXEC(@strsql)的區(qū)別。大家可以去找資料了解下。
關(guān)于分頁(yè)的一些事就寫(xiě)到這了，僅供參考。
分享標(biāo)題：分頁(yè)存儲(chǔ)過(guò)程實(shí)例剖析心得
本文鏈接：http://m.5511xx.com/article/cdddssp.html
