-- =============================================
-- 檔案名 P_Page
-- Copyright(c) 大理資訊
-- 創建人 limin_he(limin_he@maxense.com)
-- 創建日期 2006/09/08
-- 描述 利用SQL查詢語句進行分頁
-- 輸入
-- {
-- @SQL : SQL查詢語句
-- @Order : 排序欄位
-- @CurPage : 當前頁
-- @PageRows : 每頁顯示行數
-- @TotalRecorder: 查詢記錄總數(輸出參數)
-- }
-- 輸出
-- {
-- BookTypeID ,BookTypeID ,BookTypeNameE ,Description ,Sequence ,BookNum ,BookNum ,EditPerson
-- }
-- =============================================
CREATE PROCEDURE [dbo].[P_Page]
-- Add the parameters for the stored procedure here
@SQL Nvarchar(2000),
@Order Nvarchar(20),
@CurPage int,
@PageRows int,
@TotalRecorder int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
declare @ExceSQL nvarchar(4000)
--設置開始行號
declare @start_row_num AS int
SET @start_row_num = (@CurPage - 1) * @PageRows
--設置標識語句
declare @RowNumber nvarchar(100)
set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order + ') as RowNumber from '
set @SQL = Replace(@SQL,' from ',@RowNumber)
--獲得總記錄數
set @ExceSQL = 'WITH tmp AS (' + @SQL + ')
select @TotalRecorder=max(RowNumber) from tmp'
execute sp_executesql @ExceSQL,N'@TotalRecorder int output',@TotalRecorder output
--設置查詢語句
set @ExceSQL = 'WITH tmp AS (' + @SQL + ')
select * from tmp where RowNumber between ' + Convert(nvarchar,@start_row_num)
+ ' And ' + Convert(nvarchar,@start_row_num+@PageRows)
execute(@ExceSQL)
END
SQl2005分頁存儲過程
最新推荐文章于 2024-11-09 16:20:10 发布