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注入
❷ sqlserver中定義好分頁的存儲過程,怎麼使用存儲過程查詢
試試：
private DataTable GetDataByPageProc(String TableName, String Primarykey, String FieldsName, String ByWHERE
, String ByOrder, int PageSize, int PageIndex, ref int RecordCount, ref int PageCount)
{
SqlConnection cn = new SqlConnection(Function.ConnectionString);
String sql = "exec P_SlipPage @TableName=@a,@Primarykey=@b,@FieldsName=@c,@ByWHERE=@d ,@ByOrder=@e ,@PageSize=@f ,@PageIndex=@g ,@RecordCount=@h ,@PageCount =@i";
using (SqlDataAdapter da = new SqlDataAdapter(sql, cn))
{
da.SelectCommand.Parameters.AddWithValue("@a", TableName);
da.SelectCommand.Parameters.AddWithValue("@b", Primarykey);
da.SelectCommand.Parameters.AddWithValue("@c", FieldsName);
da.SelectCommand.Parameters.AddWithValue("@d", ByWHERE);
da.SelectCommand.Parameters.AddWithValue("@e", ByOrder);
da.SelectCommand.Parameters.AddWithValue("@f", PageSize);
da.SelectCommand.Parameters.AddWithValue("@g", PageIndex);
da.SelectCommand.Parameters.AddWithValue("@h", RecordCount);
da.SelectCommand.Parameters.AddWithValue("@i", PageCount);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
❸ 那位大俠幫我寫個 SqlServer分頁存儲過程 傳進去的參數有pageIndex, pageSize, rowCount ,pageCount
存儲過程本身不難寫，關鍵是後台取數據時 必須要用滾動游標，將取到的數據放到一個
臨時表裡邊。
❹ sqlserver 存儲過程分頁
你可以採用 not in 方法
set @sql=' select top '+Str(@pageSize)+' * from dbo.LogManage where LogId not in(select top '+Str((@pageNumber-1)*@pageSize)+' LogId from LogManage order by LogId asc) as t'
❺ SqlServer寫真分頁 存儲過程報錯 求大家幫忙幫忙看下消息 執行存儲後 '@PageSize1' 附近有語法錯誤。
alter proc P_GetPagedAttManagerByCondition
@DeptID int,
@PageSize int,--一頁顯示的幾條數據
@PageIndex int--當前是第幾頁
as
begin
Declare @sql1 nvarchar(2000),@sql2 nvarchar(2000),@sql nvarchar(2000)
set @sql1='select top (@PageSize1) u.UserID,u.UserName,d.DeptName from UserInfo u inner join Department d on u.DeptID=d.DeptID where u.DeptID=@id'
set @sql2='select top (@PageSize1*(@PageIndex1-1)) u.UserID from UserInfo u inner join Department d on u.DeptID=d.DeptID where u.DeptID=@id'
set @sql=@sql1+' and u.UserID not in'+'('+@sql2+')'
exec sp_executesql @sql,N'@id int,@PageSize1 int,@PageIndex1 int',@DeptID,@PageSize,@PageIndex
end
--set @sql1='select top (@PageSize1) 這里需要括弧
-- set @sql=@sql1+' and 這里需要空格
❻ sql分頁，不用存儲過程
這個要看資料庫來得，
不同的資料庫，
處理機制不一樣。
例如:
要求
查詢
SALE_REPORT
表中，每日銷售金額(SALE_MONEY)合計最大的10條數據，要求按從大到小，取第11條到第20條。
對於
Oracle
資料庫，
一般是用
ROWNUM
來處理。
SELECT
*
FROM
(
SELECT
ROWNUM
AS
NO,
A.SALE_DATE,
A.SUM_MONEY
FROM
(
SELECT
SALE_DATE,
SUM(SALE_MONEY)
AS
SUM_MONEY
FROM
SALE_REPORT
GROUP
BY
SALE_DATE
ORDER
BY
SUM(SALE_MONEY)
DESC
)
A
)
B
WHERE
B.NO
BETWEEN
11
AND
20
對於
SQL
Server
來說，
一般是使用
2個
Top
來處理
SELECT
TOP
10
top20.*
FROM
(
SELECT
TOP
20
SALE_DATE,
SUM(SALE_MONEY)
AS
SUM_MONEY
FROM
SALE_REPORT
GROUP
BY
SALE_DATE
ORDER
BY
SALE_DATE
DESC
)
AS
top20
ORDER
BY
top20.SALE_DATE
ASC
對於
Mysql
來說，
用
Limit
真是
安逸啊
SELECT
SALE_DATE,
SUM(SALE_MONEY)
AS
SUM_MONEY
FROM
SALE_REPORT
GROUP
BY
SALE_DATE
ORDER
BY
SUM(SALE_MONEY)
DESC
LIMIT
11,
10
❼ SQLServer寫一個分頁的存儲過程，要求取出101條到110條的內容，請問怎麼寫請給出具體的sql語句，謝謝。
轉載的，能用！
------------
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 @d datetime
set @d=getdate()
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)
select datediff(ms,@d,getdate()) as 查詢時間毫秒
go
Pagination 'CopyrightInformation','*','id',120,45667,0,0,''
❽ SQLSERVER如何實現分頁查詢
寫存儲過程 ..
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE usp_Province_pagination
@PageSize INT, --每頁的顯示的行數
@AbsolutePage INT, -- 當前頁的頁數
@PageCount INT OUTPUT --總頁數
AS
DECLARE @BeginRecord INT --記錄每此從哪一行開始讀取
DECLARE @RecordCount INT --表中數據的總條數
DECLARE @sql NVARCHAR(1000)
SET @RecordCount = (SELECT count(*) FROM Province)
--表中沒有數據的情況
IF @RecordCount = 0
BEGIN
SET @PageCount = 0
RETURN(0)
END
-- 表中的總條數大於定義的每頁的行數的情況
IF @RecordCount > @PageSize
BEGIN
-- 計算總能分多少頁
SET @PageCount = (@RecordCount + @PageSize - 1)/@PageSize
--當前應該從哪一行開始讀取
SET @BeginRecord = ((@AbsolutePage-1) * @PageSize)
SET @sql = N'SELECT TOP ' + cast(@PageSize AS NVARCHAR(100)) +' ProvinceID, provinceCode, ProvinceName
FROM Province
WHERE ProvinceID NOT IN
(SELECT TOP '+ CAST(@BeginRecord AS NVARCHAR(100)) + ' ProvinceID
FROM Province)'
EXECUTE sp_executesql @sql
END
ELSE -- -- 表中的總條數大於定義的每頁的行數情況
BEGIN
SET @PageCount = 1
SET @SQL = 'SELECT ProvinceID, provinceCode, ProvinceName FROM Province '
EXECUTE sp_executesql @sql
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
❾ SQL分頁存儲
SQLSERVER分頁：
CREATE PROCEDURE UP_GetDataList
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的欄位名
@PageSize int = 10, -- 頁尺寸
@PageIndex int = 1, -- 頁碼
@doCount bit = 0, -- 返回記錄總數, 非 0 值則返回
@OrderType bit = 0, -- 設置排序類型, 非 0 值則降序
@strWhere nvarchar(1000) = '' -- 查詢條件 (注意: 不要加 where)
AS
declare @strSQL nvarchar(4000) -- 主語句
declare @strTmp varchar(110) -- 臨時變數
declare @strOrder varchar(400) -- 排序類型
if @doCount != 0 --如果@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
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
----------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: David.Yan
-- Create date: 2007.12.17
-- Description:
-- =============================================
Create PROC P_viewPage
-- Add the parameters for the stored procere here
@TableName VARCHAR(200), --表名
@FieldList VARCHAR(2000), --顯示列名，如果是全部欄位則為*
@PrimaryKey VARCHAR(100), --單一主鍵或唯一值鍵
@Where VARCHAR(2000), --查詢條件 不含'where'字元，如id>10 and len(userid)>9
@Order VARCHAR(1000), --排序 不含'order by'字元，如id asc,userid desc，必須指定asc或desc
--注意當@SortType=3時生效，記住一定要在最後加上主鍵，否則會讓你比較郁悶
@SortType INT, --排序規則 1:正序asc 2:倒序desc 3:多列排序方法
@RecorderCount INT, --記錄總數 0:會返回總記錄
@PageSize INT, --每頁輸出的記錄數
@PageIndex INT, --當前頁數
@TotalCount INT OUTPUT, --記返回總記錄
@TotalPageCount INT OUTPUT --返回總頁數
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0
SET @Order = RTRIM(LTRIM(@Order))
SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')
WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0
BEGIN
SET @Order = REPLACE(@Order,', ',',')
SET @Order = REPLACE(@Order,' ,',',')
END
IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = ''
OR ISNULL(@PrimaryKey,'') = ''
OR @SortType < 1 OR @SortType >3
OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
BEGIN
PRINT('ERR_00參數錯誤')
RETURN
END
IF @SortType = 3
BEGIN
IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')
BEGIN
PRINT('ERR_02排序錯誤') RETURN END
END
DECLARE @new_where1 VARCHAR(1000)
DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000)
DECLARE @new_order2 VARCHAR(1000)
DECLARE @new_order3 VARCHAR(1000)
DECLARE @Sql VARCHAR(8000)
DECLARE @SqlCount NVARCHAR(4000)
IF ISNULL(@where,'') = ''
BEGIN
SET @new_where1 = ' '
SET @new_where2 = ' WHERE '
END
ELSE
BEGIN
SET @new_where1 = ' WHERE ' + @where
SET @new_where2 = ' WHERE ' + @where + ' AND '
END
IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2
BEGIN
IF @SortType = 1
BEGIN
SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC'
SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC'
END
IF @SortType = 2
BEGIN
SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC'
SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC'
END
END
ELSE
BEGIN
SET @new_order1 = ' ORDER BY ' + @Order
END
IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0
BEGIN
SET @new_order1 = ' ORDER BY ' + @Order
SET @new_order2 = @Order + ','
SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
IF @FieldList <> '*'
BEGIN
SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')
SET @FieldList = ',' + @FieldList
WHILE CHARINDEX(',',@new_order3)>0
BEGIN
IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0
BEGIN
SET @FieldList =
@FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))
END
SET @new_order3 = SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
END
SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))
END
END
SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+ CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1
IF @RecorderCount = 0
BEGIN
EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
@TotalCount OUTPUT,@TotalPageCount OUTPUT
END
ELSE
BEGIN
SELECT @TotalCount = @RecorderCount
END
IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
END
IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
IF @PageIndex = 1 --返回第一頁數據
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order1
END
IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最後一頁數據
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
+ ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1
END
END
ELSE
BEGIN
IF @SortType = 1 --僅主鍵正序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向檢索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
+ '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1
END
ELSE --反向檢索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'SELECT TOP ' + STR(@PageSize) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
+ ' ) AS TMP ' + @new_order1
END
END
IF @SortType = 2 --僅主鍵反序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向檢索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
+' FROM '+ @TableName
+ @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1
END
ELSE --反向檢索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'SELECT TOP ' + STR(@PageSize) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
+ '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
+ ' ) AS TMP ' + @new_order1
END
END
IF @SortType = 3 --多列排序，必須包含主鍵，且放置最後，否則不處理
BEGIN
IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0
BEGIN
PRINT('ERR_02') RETURN
END
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向檢索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
+ @new_order2 + ' ) AS TMP ' + @new_order1
END
ELSE --反向檢索
BEGIN
SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ ' SELECT TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1 + ' ) AS TMP ' + @new_order1
END
END
END
EXEC(@Sql)
GO
MYSQL分頁：
DELIMITER $$
DROP PROCEDURE IF EXISTS `p_pageList`$$
/* m_pageNo 當前頁碼;
m_perPageCnt int ,每頁顯示條數;
m_column,查詢的欄位字元;
m_table,查詢的表名;
m_condition,where條件(不用寫where);
m_orderBy,排序條件(不用寫order by);
m_totalPageCnt,查找的總條數*/
CREATE DEFINER=`root`@`%` PROCEDURE `p_pageList`(
m_pageNo int ,
m_perPageCnt int ,
m_column varchar(1000) ,
m_table varchar(1000) ,
m_condition varchar(1000),
m_orderBy varchar(200) ,
out m_totalPageCnt int
)
BEGIN
SET @pageCnt = 1; -- 總記錄數
SET @limitStart = (m_pageNo - 1)*m_perPageCnt;
SET @limitEnd = m_perPageCnt;
SET @sqlCnt = CONCAT('select count(1) into @pageCnt from ',m_table); -- 這條語句很關鍵，用來得到總數值
-- @pageCnt變數為統計信息數
SET @sql = CONCAT('select ',m_column,' from ',m_table);
IF m_condition IS NOT NULL AND m_condition <> '' THEN -- 如果條件不為空
SET @sql = CONCAT(@sql,' where ',m_condition); -- 這條SQL語句為查找條件信息
SET @sqlCnt = CONCAT(@sqlCnt,' where ',m_condition); -- 這條SQL語句為查找條件信息的總數量
END IF;
IF m_orderBy IS NOT NULL AND m_orderBy <> '' THEN -- 如果排序條件不為空
SET @sql = CONCAT(@sql,' order by ',m_orderBy); -- SQL語句追加排序
END IF;
SET @sql = CONCAT(@sql, ' limit ', @limitStart, ',', @limitEnd); -- SQL語句添加分頁信息
PREPARE s_cnt from @sqlCnt; -- 預處理SQL語句
EXECUTE s_cnt; -- 執行SQL語句
DEALLOCATE PREPARE s_cnt;
SET m_totalPageCnt = @pageCnt; -- 將值輸出給 m_totalPageCnt變數
PREPARE record from @sql;
EXECUTE record;
DEALLOCATE PREPARE record;
END$$
DELIMITER ;
這兩個應該都容易看懂，就算看不懂也沒關系，只要知道傳參數用就可以了。
❿ 求Sql Server2005分頁存儲過程
/*=======================================2008.3.19 笨笨熊 18071777=============================================================*/
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;
}