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
⑵ 如何利用 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
⑶ SQL Server 2005存儲過程分頁排序設置列名為變數
用字元串的方式來做,先生成查詢字元串,然後執行
/*******************************************************************************
Author : Richard.Cai
CreateDate : 2008-12-22
UpdateDate : 2008-12-25
Amendment History:
Update by $$$$$$$$$$$ 2008/12/25 : Alter content.
*******************************************************************************/
ALTER PROCEDURE [dbo].[USP_AT_MR_SearchClient]
(
@PageSize INT = 10 --The size per page
,@PageStart INT = 0 --The page start,not contain this no..
,@WhereStr NVARCHAR(MAX) = '' --For example:' AND ClientName like '%AS%''
,@DebugFlag BIT = 0
)
AS
DECLARE @TableName NVARCHAR(255) --Only a table name
DECLARE @FieldName NVARCHAR(255) --Field name that data is unique.
DECLARE @sSQL NVARCHAR(MAX)
DECLARE @sTMP NVARCHAR(MAX)
DECLARE @sOrderBy NVARCHAR(255)
/*Generate Paging Data*/
SELECT @sSQL=N'SELECT '+CHAR(13)
+N' ROW_NUMBER() OVER(ORDER BY SysID) AS PageID '+CHAR(13)
+N' ,Client_id As ClientCode '+CHAR(13)
+N' ,CAST(RTRIM(Client_e) AS NVARCHAR(50)) As ClientName '+CHAR(13)
+N' ,CAST(RTRIM(Client_c) AS NVARCHAR(50)) As ClientNameLocal '+CHAR(13)
+N'INTO #Client '+CHAR(13)
+N'FROM Client '+CHAR(13)
+N'WHERE 1 = 1 ' + @WhereStr +CHAR(13)
+N' '+CHAR(13)
+N'SELECT * FROM #Client '+CHAR(13)
+N'WHERE PageID > ' + RTRIM(@PageStart) + ' AND PageID <= ' + RTRIM(@PageStart+@PageSize)+CHAR(13)
+N' '
+N'SELECT COUNT(*) As TotalCount FROM #Client'
IF @DebugFlag = 1
PRINT '/*Paging SQL:*/'+CHAR(13)+@sSQL
EXEC SP_EXECUTESQL @sSQL
⑷ 怎樣調 sql server 2005 存儲過程分頁
首先建立好SQL 分頁存儲過程，使用的時候在資料庫運行一下就可以了。
⑸ 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
⑹ SQL分頁的幾種方法
方法一 使用offset fetch next（2012版本及以上版本才可以使用）
方法二 使用row_number()函數
利用row_number() over(order by id desc)函數計算出行數，選定相應的行數返回即可（2005版本以上才可以使用）
使用存儲過程封裝
⑺ sql存儲過程分頁
因為這時動態字元串拼接,如果不轉換會報錯的，會把+當作加法符號
⑻ Sqlserver2005的存儲過程分頁轉換成Sqlserver 2000的
create proc GetCoumterPage
@currentIndex int,--當前頁
@pagesize int,--顯示的行數
@pageCount int output--總頁碼數
as
declare
@rowcount int
set @rowcount=(select count(*) from Singles)
if(@rowcount%@pagesize>0)
set @pagecount =(@rowcount/@pagesize)+1
else
set @pagecount=@rowcount/@pagesize
select identity(int,1,1) AS rankid,id INTO #t
From Singles
select*from
(
select rankid,a.id,title,[content],operating,photo,pdf,remark from Singles a inner join #t b on a.id=b.id
)
as SinglesInfo where rankid >@currentIndex*@pagesize and rankid<=@currentIndex*@pagesize+@pagesize
⑼ 求SQL2005高性能存儲過程分頁代碼，該怎麼處理
Create procere [dbo].[Pager]
(
@Psql nvarchar(4000), --生成dataset的語句
@PNum int, --顯示第幾頁
@PSize int, --顯示多少條
@Sort nvarchar(200) = null, --排序語句 如:order by id desc
@RowNumName nvarchar(50), --ROW_NUMBER別名
@Prcount int out, --返回記錄總數
@Pcount int out --返回分頁總數
)
as
set nocount on
declare @sqlTmp nvarchar(1000) --存放SQL語句
declare @sqlTmpCount nvarchar(1000) --存放查詢記錄總數量SQL語句
--計算范圍
declare @Pmax int
declare @Pmin int
set @Pmax = @pnum*@psize --當前頁*頁大小 = 頁最大值
set @Pmin = @Pmax - @psize +1 --頁最大值 - 頁大小 + 1 = 頁最小值
set @sqlTmp='select * from ('+@Psql+')as temptb where '+@rowNumName+' BETWEEN '+cast(@Pmin as varchar(4))+' and '+cast(@Pmax as varchar(4))+''+@sort
set @sqlTmpCount='select @Prcount=count(*) from ('+@Psql+') as temptb'
----取得查詢記錄總數量-----
exec sp_executesql @sqlTmpCount,N'@Prcount int out ',@Prcount out
--取得分頁總數
set @Pcount=(@Prcount+@pSize-1)/@pSize
exec sp_executesql @sqlTmp
set nocount off
/*調用測試
declare @Prcount int
declare @Pcount int
exec Pager 'select row_number() over(order by picid) AS rownum,* FROM userpic',1,3,'','rownum',1,1
print @Pcount*/
業務層調用：
/// <summary>
/// 根據用戶自定義商品排序,分頁取得用戶收藏的商品
/// </summary>
/// <param name="pageIndex">當前頁索引</param>
/// <param name="pageSize">每頁記錄數</param>
/// <param name="recordCount">輸出參數:總記錄數</param>
/// <param name="pageCount">輸出參數:總頁數</param>
/// <returns>數據集(DataSet)</returns>
public DataSet GetShopsByPage(int pageIndex, int pageSize, out int recordCount, out int pageCount)
{
sql = "select row_number() over(order by picid) AS rownum,* FROM userpic";
return objDAO.GetDataByPage(sql, pageIndex, pageSize, "", "rownum", out recordCount, out pageCount);
}
數據訪問層方法：
/// <summary>
/// 函數: 執行分頁存儲過程 , 返回數據集,適用於Sqlserver2005資料庫
/// </summary>
/// <param name="psql">生成dataset的語句</param>
/// <param name="pNum">顯示第幾頁</param>
/// <param name="pSize">顯示多少條</param>
/// <param name="sort">排序語句 如:order by id desc </param>
/// <param name="rowNumName">ROW_NUMBER別名</param>
/// <param name="rCount">記錄總數</param>
/// <param name="pCount">頁總數</param>
/// <returns>返回數據集</returns>
public override DataSet GetDataByPage(string psql, int pNum, int pSize, string sort, string rowNumName, out int rCount, out int pCount)
{
OpenConn();
sqlCmd = new SqlCommand("Pager", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcere;
sqlCmd.Parameters.Add("@Psql", SqlDbType.NVarChar).Value = psql;
sqlCmd.Parameters.Add("@pNum", SqlDbType.Int).Value = pNum;
sqlCmd.Parameters.Add("@Psize", SqlDbType.Int).Value = pSize;
sqlCmd.Parameters.Add("@sort", SqlDbType.NVarChar).Value = sort;
sqlCmd.Parameters.Add("@rowNumName", SqlDbType.NVarChar).Value = rowNumName;
sqlCmd.Parameters.Add("@Prcount", SqlDbType.Int).Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add("@Pcount", SqlDbType.Int).Direction = ParameterDirection.Output;
//數據集對象
ds = new DataSet();
sqlDa = new SqlDataAdapter();
sqlDa.SelectCommand = sqlCmd;
//數據填充
sqlDa.Fill(ds);
//從輸出參數中讀取[總記錄數]
rCount = (int)sqlCmd.Parameters["@Prcount"].Value;
//從輸出參數中讀取[總頁數]
pCount = (int)sqlCmd.Parameters["@Pcount"].Value;
CloseConn();
return ds;
}
⑽ 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 語言對分頁功能的支持進步不少。使分頁實現趨向於簡單化。