sqlserver2005分頁
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
② 如何用sql語句 實現分頁查詢
適用於 SQL Server 2000/2005
SELECT TOP 頁大小 *
FROM table1
WHERE id NOT IN
SELECT TOP 頁大小*(頁數-1) id FROM table1 ORDER BY id
③ SQL Server 分頁 查詢語句
四種方式實現SQLServer 分頁查詢
SQLServer 的數據分頁:
假設現在有這樣的一張表：
CREATE TABLE test
(
id int primary key not null identity,
names varchar(20)
)
然後向裡面插入大約1000條數據，進行分頁測試
假設頁數是10，現在要拿出第5頁的內容，查詢語句如下：
--10代表分頁的大小
select top 10 *
from test
where id not in
(
--40是這么計算出來的：10*(5-1)
select top 40 id from test order by id
)
order by id
原理：需要拿出資料庫的第5頁，就是40-50條記錄。首先拿出資料庫中的前40條記錄的id值，然後再拿出剩餘部分的前10條元素
第二種方法：
還是以上面的結果為例，採用另外的一種方法
--數據的意思和上面提及的一樣
select top 10 *
from test
where id >
(
select isnull(max(id),0)
from
(
select top 40 id from test order by id
) A
)
order by id
原理：先查詢前40條記錄，然後獲得其最id值，如果id值為null的，那麼就返回0
然後查詢id值大於前40條記錄的最大id值的記錄。
這個查詢有一個條件，就是id必須是int類型的。
第三種方法：
select top 10 *
from
(
select row_number() over(order by id) as rownumber,* from test
) A
where rownumber > 40
原理：先把表中的所有數據都按照一個rowNumber進行排序，然後查詢rownuber大於40的前十條記錄
這種方法和oracle中的一種分頁方式類似，不過只支持2005版本以上的
第四種：
存儲過程查詢
創建存儲過程
alter procere pageDemo
@pageSize int,
@page int
AS
declare @temp int
set @temp=@pageSize*(@page - 1)
begin
select top (select @pageSize) * from test where id not in (select top (select @temp) id from test) order by id
end
執行存儲過程
exec 10,5
④ 如何利用 vc ado 連接 Sql Server2005 實現分頁存儲過程
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Proc_Util_Page]') AND OBJECTPROPERTY(id, N'IsProcere') = 1)
DROP PROCEDURE dbo.Proc_Util_Page
GO
/*
分頁
調用方法:EXEC Proc_Util_Page 'country,city', 'table', ' where 1=1', ' order by mid asc', 'mid', 1, 1, 0, ''
輸入:
1.欄位(不可為空)
2.表名(不可為空)
3.條件(可以為空,需要where)
4.排序(可以為空,需要order by,需要asc和desc字元)
5.主鍵(可以為空)
6.當前頁數
7.每頁記錄數
8.輸出總記錄條數(若<1則執行count)
9.輸出sql語句
返回:記錄集
*/
CREATE PROCEDURE dbo.Proc_Util_Page
(
@sField nvarchar(1000),
@sTable nvarchar(1000),
@sWhere nvarchar(1000),
@sOrderby nvarchar(1000),
@sPkey nvarchar(50),
@iPageIndex int,
@iPageSize int,
@iRecordCount int OUTPUT,
@sOutsql nvarchar(4000) OUTPUT
)
--WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
DECLARE @iRC int, @sSQL nvarchar(4000), @sW nvarchar(1000), @sOB nvarchar(1000), @sT nvarchar(100)
SELECT @iRC = @iRecordCount, @sSQL = '', @sW = ' WHERE 1=1 ', @sOB = ''
--判斷條件
IF RTRIM(@sWhere) != '' AND @sWhere IS NOT NULL
BEGIN
SET @sW=' ' + @sWhere + ' '
END
--判斷總記錄數
IF @iRC<1
BEGIN
SET @sSQL='SELECT @iRC=Count(*) FROM ' + @sTable + @sW
EXEC sp_executesql @sSQL,N'@iRC int OUT',@iRC OUT
END
--判斷頁數是否超出范圍
SELECT @iPageIndex=(CASE WHEN @iRC<(@iPageIndex-1)*@iPageSize THEN CEILING(@iRC/@iPageSize) WHEN @iPageIndex<1 THEN 1 ELSE @iPageIndex END)
--判斷排序
IF RTRIM(@sOrderby) != '' AND @sOrderby IS NOT NULL
BEGIN
SELECT @sOB=' ' + @sOrderby + ' '
END
--如果是第一頁
IF @iPageIndex=1
BEGIN
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW+@sOB
GOTO step4
END
--看有否主鍵
IF RTRIM(@sPkey) = '' OR @sPkey IS NULL
GOTO step1
ELSE
--看是否按主鍵排序
BEGIN
DECLARE @sOB1 nvarchar(1000), @sPkey1 nvarchar(50)
SELECT @sOB1 = UPPER(@sOrderby), @sPkey1 = UPPER(@sPkey)
IF CHARINDEX(@sPkey1 + ' ASC', @sOB1)>0
BEGIN
SET @sT='>(SELECT MAX('
GOTO step2
END
IF CHARINDEX(@sPkey1 + ' DESC', @sOB1)>0
BEGIN
SET @sT='<(SELECT MIN('
GOTO step2
END
GOTO step3
END
--如果無主鍵
step1:
BEGIN
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW + ' AND EXISTS (SELECT TOP '+CAST((@iPageIndex-1)*@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW+@sOB+')'+@sOB+')'
GOTO step4
END
--純按主鍵排序
step2:
BEGIN
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW+' AND '+@sPkey+@sT+@sPkey+') FROM (SELECT TOP '+CAST((@iPageIndex-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sW+@sOB+') AS tbTemp)'+@sOB
GOTO step4
END
--不純按主鍵排序
step3:
BEGIN
SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sW + ' AND ' + @sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sW + ' AND ' + @sPkey+' NOT IN(SELECT TOP '+CAST((@iPageIndex-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sW+@sOB+')'+@sOB+')'+@sOB
GOTO step4
END
--輸出最終執行的分頁sql語句並執行
step4:
SELECT @sOutsql = @sSQL, @iRecordCount = @iRC
--print(@sSQL)
EXEC(@sSQL)
SET NOCOUNT OFF
END
GO
---調用分頁存儲過程
declare @iRecordCount int,
@sOutsql nvarchar(4000)
EXEC Proc_Util_Page '欄位,欄位,欄位,欄位,欄位', '表名', ' where 1=1', ' order by id asc', 'id', 1, 10, @iRecordCount output, @sOutsql output
⑤ sqlserver 2005資料庫 怎麼進行分頁
分兩步實現
一、分頁的存儲過程如下
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[Pagination]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) , -- 需要返回的列
@fldName varchar(255), -- 排序的欄位名
@PageSize int, -- 頁尺寸
@PageIndex int , -- 頁碼
@doCount bit=0, -- 返回記錄總數, 非 0 值則返回
@OrderType bit, -- 設置排序類型, 非 0 值則降序
@strWhere varchar(1500) -- 查詢條件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主語句
declare @strTmp varchar(110) -- 臨時變數
declare @strOrder varchar(400) -- 排序類型
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
--以上代碼的意思是如果@doCount傳遞過來的不是0，就執行總數統計。以下的所有代碼都是@doCount為0的情況
else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName + '] desc'
end
--如果@OrderType不是0，就執行降序，這句很重要！
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName + '] asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from [' + @tblName + '] where '
+ @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields
+ ' from [' + @tblName + '] ' + @strOrder
end
--如果是第一頁就執行以上代碼，這樣會加快執行速度
else
begin
--以下代碼賦予了@strSQL以真正執行的SQL代碼
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top '
+ str( ( @PageIndex - 1 ) * @PageSize ) + ' ['+ @fldName + '] from [' + @tblName + ']'
+ @strOrder + ') as tblTmp)' + @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str( ( @PageIndex - 1 ) * @PageSize ) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec(@strSQL)
二、頁面調用部分代碼
Function navindex(ByVal PageIndextemp As Integer, ByVal PageSizetemp As Integer, ByVal countint As Integer, ByVal pagename As String) As String
Dim i As Integer
If countint Mod PageSizetemp = 0 Then
i = countint \ PageSizetemp
Else
i = countint \ PageSizetemp + 1
End If
Dim maxi, mini As Integer
Dim navleft, navright, navstrtemp As String
If i < 10 Then
maxi = i
mini = 1
Else
maxi = pageindex + 3
mini = pageindex - 3
If mini > 1 Then
navleft = "<a href=""" & pagename & "?page=" & (mini - 1) & """ class=""link_nav_btn""><</a> "
Else
mini = 1
maxi = 10
End If
If maxi < i Then
navright = " <a href=""" & pagename & "?page=" & (maxi + 1) & """ class=""link_nav_btn"">></a>"
Else
If i - 10 > 0 Then
mini = i - 10
Else
mini = 1
End If
maxi = i
End If
End If
For n As Integer = mini To maxi
If n = pageindex Then
navstrtemp = navstrtemp & " <a href=""" & pagename & "?page=" & n & """ class=""link_nav_btn_select""><b>" & n & "</b></a>"
Else
navstrtemp = navstrtemp & " <a href=""" & pagename & "?page=" & n & """ class=""link_nav_btn"">" & n & "</a>"
End If
Next
navstrtemp = navleft & navstrtemp & navright
Return navstrtemp
End Function
Sub databinds(ByVal tblnametemp As String, ByVal strGetFieldstemp As String, ByVal fldNametemp As String, ByVal PageSizetemp As Integer, ByVal PageIndextemp As Integer, ByVal OrderTypetemp As Short, ByVal strWheretemp As String)
'tblnametemp表名,strGetFieldstemp需要返回的列,fldNametemp排序的欄位名,PageSizetemp頁尺寸,PageIndextemp頁碼,OrderTypetemp設置排序類型,strWheretemp查詢條件
'總數
cmdTM = New SqlCommand("select count(*) from " & tblnametemp & " where " & strWheretemp, conPubs)
conPubs.Open()
countint = CInt(cmdTM.ExecuteScalar())
conPubs.Close()
'導航
navstr = navindex(PageIndextemp, PageSizetemp, countint, "newshyxh.aspx")
'分頁
cmdTM = New SqlCommand("Pagination", conPubs)
cmdTM.CommandType = CommandType.StoredProcere
'add input
cmdTM.Parameters.Add("@tblName", SqlDbType.VarChar, 255).Value = tblnametemp
cmdTM.Parameters.Add("@strGetFields", SqlDbType.VarChar, 1000).Value = strGetFieldstemp
cmdTM.Parameters.Add("@fldName", SqlDbType.VarChar, 255).Value = fldNametemp
cmdTM.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndextemp
cmdTM.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSizetemp
cmdTM.Parameters.Add("@OrderType", SqlDbType.Bit).Value = OrderTypetemp
cmdTM.Parameters.Add("@strWhere", SqlDbType.VarChar, 1500).Value = strWheretemp
conPubs.Open()
newsright.DataSource = cmdTM.ExecuteReader()
newsright.DataBind()
conPubs.Close()
End Sub