sqlserver2008存儲過程分頁

㈠ sql server 2008 用row_number() over()怎麼分頁

主要採用row_number對其進行編號，然後根據頁數取出相應的編號，具體的原理，你可以看一下這里

row_number分頁原理

如有疑問，及時溝通！

㈡ sql server 分頁存儲過程

CREATE PROCEDURE pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的欄位名
@PageSize int = 10, -- 頁尺寸
@PageIndex int = 1, -- 頁碼
@doCount bit = 0, -- 返回記錄總數, 非 0 值則返回
@OrderType bit = 0, -- 設置排序類型, 非 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'
--如果@OrderType不是0，就執行降序，這句很重要！
end
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)
GO

/*
-- 需要傳遞的參數
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的欄位名
@PageSize int = 10, -- 頁尺寸
@PageIndex int = 1, -- 頁碼
@doCount bit = 0, -- 返回記錄總數, 非 0 值則返回
@OrderType bit = 0, -- 設置排序類型, 非 0 值則降序
@strWhere varchar(1500) = '' -- 查詢條件 (注意: 不要加 where)
-- 調用測試

exec pagination @tblName='jobs', @strGetFields='job_id,job_desc,min_lvl,max_lvl',
@fldName='job_id',@PageSize=3,@PageIndex=1,
@doCount=0,@OrderType=1,@strWhere=''
*/
============================================

CREATE PROC SP_PageList
@tbname sysname, --要分頁顯示的表名
@FieldKey sysname, --用於定位記錄的主鍵(惟一鍵)欄位,只能是單個欄位
@PageCurrent int=1, --要顯示的頁碼
@PageSize int=10, --每頁的大小(記錄數)
@FieldShow nvarchar(1000)='', --以逗號分隔的要顯示的欄位列表,如果不指定,則顯示所有欄位
@FieldOrder nvarchar(1000)='', --以逗號分隔的排序欄位列表,可以指定在欄位後面指定DESC/ASC
--用於指定排序順序
@Where nvarchar(1000)='', --查詢條件
@RecordCount int OUTPUT, --總記錄數
@PageCount int OUTPUT --總頁數
AS
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON
--檢查對象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'對象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、視圖或者表值函數',1,16,@tbname)
RETURN
END

--分頁欄位檢查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分頁處理需要主鍵（或者惟一鍵）',1,16)
RETURN
END

--其他參數檢查及規范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'

--如果@PageCount為NULL值,則計算總頁數(這樣設計可以只在第一次計算總頁數,以後調用時,把總頁數傳回給存儲過程,避免再次計算總頁數,對於不想計算總頁數的處理而言,可以給@PageCount賦值)
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @PageCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @RecordCount = @PageCount
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END

--計算分頁顯示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize

--第一頁直接顯示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
SELECT @PageCurrent=@TopN1,
@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
+N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql,
N'@n int,@s nvarchar(4000) OUTPUT',
@PageCurrent,@sql OUTPUT
SET ROWCOUNT 0
IF @sql=N''
EXEC(N'SELECT TOP 0'
+N' '+@FieldShow
+N' FROM '+@tbname)
ELSE
BEGIN
SET @sql=STUFF(@sql,1,1,N'')
--執行查詢
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' WHERE '+@FieldKey
+N' IN('+@sql
+N') '+@FieldOrder)
END
END
GO

㈢ sqlserver2008從一個表中模糊查詢並分頁

select * from (
select * from(
select b.*,rownum rn from bbs b where title like '%呵呵%'
and rownum<=10)
where rn<=分頁的上限)
where rn>分頁的下限

㈣ （問題解決再追加100分）sql server存儲過程實現查詢數據條數過大，分頁查詢怎麼實現

按說5-8w這樣數量級的數據沒有問題，寫入Excel是布比較耗性能，主要還是要通過優化寫入Excel的代碼效率上去考慮。你可以考慮利用分批查詢寫入的方式來避免一次寫太多的數據到Excel：將你的查詢結果分段，比方你的語句中能不能用時間來認為分段，每次返回部分結果。
回到你的問題，對大數據量查詢的解決方案有以下兩種：
（1）、將全部數據先查詢到內存中，然後在內存中進行分頁，這種方式對內存佔用較大，必須限制一次查詢的數據量。
（2）、採用存儲過程在資料庫中進行分頁，這種方式對資料庫的依賴較大，不同的資料庫實現機制不通，並且查詢效率不夠理想。以上兩種方式對用戶來說都不夠友好。

2．解決思路
通過在待查詢的資料庫表上增加一個用於查詢的自增長欄位，然後採用該欄位進行分頁查詢，可以很好地解決這個問題。下面舉例說明這種分頁查詢方案。

（1）、在待查詢的表格上增加一個long型的自增長列，取名為「queryId」,mssql、sybase直接支持自增長欄位，oracle可以用sequence和trigger來實現。然後在該列上加上一個索引。
添加queryId列的語句如下：
Mssql: [QUERYID] [bigint] IDENTITY (1, 1)

Sybase: QUERYID numeric(19) identity

Oracle:
CREATE SEQUENCE queryId_S
INCREMENT BY 1
START WITH 1
MAXVALUE 999999999999999 MINVALUE 1
CYCLE
CACHE 20
ORDER;
CREATE OR REPLACE TRIGGER queryId_T BEFORE INSERT
ON "test_table"
FOR EACH ROW
BEGIN
select queryId_S.nextval into :new.queryId from al;
END;

（2）、在查詢第一頁時，先按照大小順序的倒序查出所有的queryId，
語句如下：select queryId from test_table where + 查詢條件 +order by queryId desc 。
因為只是查詢queryId欄位，即使表格中的數據量很大，該查詢也會很快得到結果。然後將得到的queryId保存在應用伺服器的一個數組中。

（3）、用戶在客戶端進行翻頁操作時，客戶端將待查詢的頁號作為參數傳遞給應用伺服器，伺服器通過頁號和queyId數組算出待查詢的queyId最大和最小值，然後進行查詢。

算出queyId最大和最小值的演算法如下,其中page為待查詢的頁號，pageSize為每頁的大小，queryIds為第二步生成的queryId數組：
int startRow = (page - 1) * pageSize
int endRow = page * pageSize - 1;
if (endRow >=queryIds.length)
{
endRow = this.queryIds.length - 1;
}
long startId =queryIds[startRow];
long endId =queryIds[endRow];

查詢語句如下：
String sql = "select * from test_table" + 查詢條件 + "(queryId <= " + startId + " and queryId >= " + endId + ")";

3．效果評價
該分頁查詢方法對所有資料庫都適用，對應用伺服器、資料庫伺服器、查詢客戶端的cpu和內存佔用都較低，查詢速度較快，是一個較為理想的分頁查詢實現方案。經過測試，查詢4百萬條數據，可以在3分鍾內顯示出首頁數據，以後每一次翻頁操作基本在2秒以內。內存和cpu佔用無明顯增長。

以上也僅僅是分頁查詢結果查看的問題，你需要寫入到Excel的話還需要考慮Excel寫入代碼的執行效率，這部分是很值得研究的。

㈤ SQL server 2008 存儲過程分頁實現，怎麼提升效率

創建存儲過程： create proc procPaging ( @tablename nvarchar(100), --表名 @column nvarchar(100)=null, --列名 @sort nvarchar(10), --排序asc，desc） @pageIndex int, --當前頁碼 @pageCount int --每頁顯示多少條數據 ) as begin declare @strSql nvarchar(max) set @strSql='select * from (select ROW_NUMBER() over(order by '+@column+''+@sort+' ) as rowid,* from '+@tablename+') newtable where rowid between '+ cast((@pageindex-1)*@pagecount+1 as nvarchar(100))+' and '+convert(varchar(20),@pageIndex*@pageCount) exec (@strSql) --執行字元串的SQL語句 end 執行存儲過程： exec procPaging 'BookShop_BookType','BookType_ID','asc',1,3 exec procPaging 'BookShop_BookType','BookType_ID','asc',2,3 刪除存儲過程: drop proc book_Paging

㈥ 資料庫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注入

㈦ SQL SERVER 2008 分頁代碼怎麼寫

select top 10 *
from (select row_number()
over(order by ID asc) as rownumber,*
from [聯系人管理表] where [民族] LIKE '%漢%') temp_row
where rownumber > 10
//把條件寫在排序語句那裡

㈧ 求個sql server2008 大數據量所用的分頁存儲過程

我有通用版的SQL存儲過程分頁。絕對好用。使用了N多項目，速度最快，數據量越大速度越快
給我QQ號，傳給你

㈨ sql存儲過程分頁 臨時表問題

sql分頁不一定用存儲過程,就過個人認為存儲過程盡量少用!
--前提是必需有一列是自動增長類型,唯一性

--方法一

SELECT DISTINCT TOP 8 CategoryID
FROM tbl_Proct_Procts
WHERE (UserID = 73) AND (CategoryID >
(SELECT MAX(categoryid)
FROM (SELECT DISTINCT TOP 16 categoryid
FROM tbl_proct_procts where userid=73
ORDER BY categoryid) AS b))
ORDER BY CategoryID

--方法二

select top 10 * from [order details]
where orderid>all(select top 10 orderid from [order details] order by orderid)
order by orderid
下面的來自CSDN-------------------------------------
SQL Server 存儲過程的分頁，這個問題已經討論過幾年了，很多朋友在問我，所以在此發表一下我的觀點
建立表：

CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

插入數據：(2萬條，用更多的數據測試會明顯一些)
SET IDENTITY_INSERT TestTable ON

declare @i int
set @i=1
while @i<=20000
begin
insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
set @i=@i+1
end

SET IDENTITY_INSERT TestTable OFF

-------------------------------------

分頁方案一：(利用Not In和SELECT TOP分頁)
語句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID

SELECT TOP 頁大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 頁大小*頁數 id
FROM 表
ORDER BY id))
ORDER BY ID

-------------------------------------

分頁方案二：(利用ID大於多少和SELECT TOP分頁）
語句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID

SELECT TOP 頁大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 頁大小*頁數 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID

-------------------------------------

分頁方案三：(利用SQL的游標存儲過程分頁)
create procere XiaoZhengGe
@sqlstr nvarchar(4000), --查詢字元串
@currentpage int, --第N頁
@pagesize int --每頁行數
as
set nocount on
declare @P1 int, --P1是游標的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 總頁數--,@rowcount as 總行數,@currentpage as 當前頁
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off

其它的方案：如果沒有主鍵，可以用臨時表，也可以用方案三做，但是效率會低。
建議優化的時候，加上主鍵和索引，查詢效率會提高。

通過SQL 查詢分析器，顯示比較：我的結論是:
分頁方案二：(利用ID大於多少和SELECT TOP分頁）效率最高，需要拼接SQL語句
分頁方案一：(利用Not In和SELECT TOP分頁) 效率次之，需要拼接SQL語句
分頁方案三：(利用SQL的游標存儲過程分頁) 效率最差，但是最為通用

在實際情況中，要具體分析。

更多的討論見：
http://community.csdn.net/Expert/topic/3292/3292678.xml?temp=.1621515

㈩ 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 語言對分頁功能的支持進步不少。使分頁實現趨向於簡單化。