mssql存儲過程分頁
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
CREATE
proc
[dbo].[up_Page2005]
@TableName
varchar(50),
--表名
@Fields
varchar(5000)
=
'*',
--欄位名(全部欄位為*)
@OrderField
varchar(5000),
--排序欄位(必須!支持多欄位)
@sqlWhere
varchar(5000)
=
Null,--條件語句(不用加where)
@pageSize
int,
--每頁多少條記錄
@pageIndex
int
=
1
,
--指定當前為第幾頁
@TotalPage
int
output
--返回總頁數
as
begin
Begin
Tran
--開始事務
Declare
@sql
nvarchar(4000);
Declare
@totalRecord
int;
--計算總記錄數
if
(@SqlWhere=''
or
@sqlWhere=NULL)
set
@sql
=
'select
@totalRecord
=
count(*)
from
'
+
@TableName
else
set
@sql
=
'select
@totalRecord
=
count(*)
from
'
+
@TableName
+
'
with(nolock)
where
'
+
@sqlWhere
EXEC
sp_executesql
@sql,N'@totalRecord
int
OUTPUT',@totalRecord
OUTPUT--計算總記錄數
--計算總頁數
select
@TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
if
(@SqlWhere=''
or
@sqlWhere=NULL)
set
@sql
=
'Select
*
FROM
(select
ROW_NUMBER()
Over(order
by
'
+
@OrderField
+
')
as
rowId,'
+
@Fields
+
'
from
'
+
@TableName
else
set
@sql
=
'Select
*
FROM
(select
ROW_NUMBER()
Over(order
by
'
+
@OrderField
+
')
as
rowId,'
+
@Fields
+
'
from
'
+
@TableName
+
'
with(nolock)
where
'
+
@SqlWhere
--處理頁數超出范圍情況
if
@PageIndex<=0
Set
@pageIndex
=
1
if
@pageIndex>@TotalPage
Set
@pageIndex
=
@TotalPage
--處理開始點和結束點
Declare
@StartRecord
int
Declare
@EndRecord
int
set
@StartRecord
=
(@pageIndex-1)*@PageSize
+
1
set
@EndRecord
=
@StartRecord
+
@pageSize
-
1
--繼續合成sql語句
set
@Sql
=
@Sql
+
')
as
t
where
rowId
between
'
+
Convert(varchar(50),@StartRecord)
+
'
and
'
+
Convert(varchar(50),@EndRecord)
print
@sql
Exec(@Sql)
---------------------------------------------------
If
@@Error
<>
0
Begin
RollBack
Tran
Return
-1
End
Else
Begin
Commit
Tran
Return
@totalRecord
---返回記錄總數
End
end
2. 資料庫sqlserver如何用存儲過程做分頁
存儲過程：create Procere pname
( @pageIndex int，@pageSize)
as
select * from tableName order by id
offset @pageIndex * pageSize fetch next pageSize rows only
分頁：
sqlserver 在2008之前 使用 top 和 not int top 的方式來做分頁
2008以後使用 row_number() 函數作為分頁關鍵函數
2012使用 offset 1 fetch next 10 rows only
你問了2個問題，你可以優先把視圖，存儲過程，觸發器等弄明白，分頁是查詢，在存儲過程里可以寫復雜的sql文，只是在運行時是預編譯和參數化查詢防止sql注入
3. SQL里存儲過程分頁問題！急！急！
CREATE PROCEDURE [Zhzuo_GetItemsPage]
@PageIndex INT, /*@PageIndex從計數,0為第一頁*/
@PageSize INT, /*頁面大小*/
@RecordCount INT OUT, /*總記錄數*/
@PageCount INT OUT /*頁數*/
AS
/*獲取記錄數*/
SELECT @RecordCount = COUNT(*) FROM Proction.Proct
/*計算頁面數據*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP記錄數*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
SET @SQLSTR =N'SELECT TOP '+STR(@PageSize)+
'ProctID,Name FROM Proction.Proct ORDER BY ProctID DESC'
END
ELSE
BEGIN
IF @PageIndex = @PageCount - 1
BEGIN
SET @SQLSTR =N'SELECT * FROM ( SELECT TOP ' + STR(@TOPCOUNT) +
'ProctID,Name FROM Proction.Proct ORDER BY ProctID ASC) T ORDER BY ProctID DESC'
END
ELSE
BEGIN
SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCOUNT) +
'ProctID,Name FROM Proction.Proct ORDER BY ProctID ASC) T ORDER BY ProctID DESC'
END
END
/*執行*/
EXEC (@SQLSTR)
以上存儲過程對頁數進行判斷，如果是第一頁或最後一頁，進行特殊處理。其他情況使用2次TOP翻轉。其中排序條件為ProctID倒序。最後通過EXECUTE執行SQL字元串拼串。
2．SQL SERVER 2005中的TOP分頁
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005TOP]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
/*獲取記錄數*/
SELECT @RecordCount = COUNT(*) FROM Proction.Proct
/*計算頁面數據*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP記錄數*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
/*基於SQL SERVER 2005 */
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
SELECT TOP(@PageSize) ProctID,Name FROM Proction.Proct ORDER BY ProctID DESC
END
ELSE
BEGIN
IF @PageIndex = @PageCount - 1
BEGIN
SELECT * FROM ( SELECT TOP(@TOPCOUNT) ProctID,Name FROM Proction.Proct ORDER BY ProctID ASC) T
ORDER BY ProctID DESC
END
ELSE
BEGIN
SELECT TOP(@PageSize) * FROM (SELECT TOP(@TOPCOUNT) ProctID,Name FROM Proction.Proct ORDER BY ProctID ASC) T
ORDER BY ProctID DESC
END
END
以上存儲過程是使用2005的TOP (表達式) 新功能，避免了字元串拼串，使結構化查詢語言變得簡潔。實現的為同樣的功能。
3．SQL SERVER 2005中的新分頁
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
/*獲取記錄數*/
SELECT @RecordCount = COUNT(*) FROM Proction.Proct
/*計算頁面數據*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/* 基於SQL SERVER 2005 */
SELECT SerialNumber,ProctID,Name FROM
(SELECT ProctID,Name,ROW_NUMBER() OVER (ORDER BY ProctID DESC) AS SerialNumber FROM Proction.Proct ) AS T
WHERE T.SerialNumber > (@PageIndex * @PageSize) and T.SerialNumber <= ((@PageIndex+1) * @PageSize)
第三個存儲過程使用2005下新的功能，實現的分頁存儲過程功能更加簡單明了，而且更加容易理解。注意這里的ProctID為主鍵,根據ProctID進行排序生成ROW_NUMBER，通過ROW_NUMBER來確定具體的頁數。
通過對三個分頁存儲過程的比較，可見SQL SERVER 的TSQL 語言對分頁功能的支持進步不少。使分頁實現趨向於簡單化。