ALTER PROCEDURE [dbo].[P_Common_Pagination]
(
@currentPage int, --当前页码
@pageSize int, --每页记录数
@querySQL nvarchar(max), --查询SQL
@orderSQL nvarchar(max), --排序SQL
@totalSQL nvarchar(max), --查询总记录数SQL
@totalCount int output, --总记录数
@newCurrentPage int output, --返回的当前页码
@exceptionMsg nvarchar(4000) output --异常信息，无异常返回的是NULL，有异常返回具体的异常信息
)
AS
declare @StartIndex int
declare @EndIndex int
declare @maxPage int
BEGIN
BEGIN TRY -- 异常捕获
set @totalSQL = N'select @totalCount=' + @totalSQL;
--执行
EXEC SP_EXECUTESQL @totalSQL,N'@totalCount int OUTPUT',@totalCount OUTPUT
--判断当前页是否超出总页
IF 0 != @totalCount
BEGIN
SET @maxPage = (@totalCount / @pageSize);
IF 0 != (@totalCount % @pageSize)
SET @maxPage = @maxPage + 1
IF @currentPage > @maxPage
SET @currentPage = @maxPage
END
ELSE
SET @currentPage = 1
SET @newCurrentPage = @currentPage
set @StartIndex = (@currentPage - 1)*@pageSize + 1;
set @EndIndex = @currentPage*@pageSize;
set @querySQL = N'SELECT *
from (
select ROW_NUMBER() over (' + @orderSQL + ') as rownum,'
+ @querySQL
+ ') tt where rownum between ' + cast(@StartIndex as varchar(50))
+ ' and ' + cast(@EndIndex as varchar(50));
EXEC (@querySQL)
END TRY
BEGIN CATCH --异常发生后，相应的事务等处理
--异常信息
set @exceptionMsg = '消息 ' + cast(ERROR_NUMBER() as varchar(50)) + '，级别 '
+ cast(ERROR_SEVERITY() as varchar(50)) + '，状态 '
+ cast(ERROR_STATE() as varchar(50)) + '，过程 ' + 'P_Common_Pagination'
+ '，第 ' + cast(ERROR_LINE() as varchar(50)) + ' 行'
+ '：' + ERROR_MESSAGE();
INSERT INTO Logger(logType, information)
VALUES('ERROR', @exceptionMsg);
END CATCH
END
分页查询实例
ALTER PROCEDURE [dbo].[P_ExceptionWaybillPageList]
(
@currentPage int, --当前页码
@pageSize int, --每页记录数
@conditionXml nvarchar(max), --条件参数XML字符串
@totalCount int output, --总记录数
@newCurrentPage int output, --返回的当前页码
@exceptionMsg nvarchar(4000) output --异常信息，无异常返回的是NULL，有异常返回具体的异常信息
)
AS
DECLARE @beforeSQL nvarchar(max) --查询SQL的头部
DECLARE @midSQL nvarchar(max) --中间公用的SQL
DECLARE @querySQL nvarchar(max) --查询SQL（去掉头部的select和尾部的排序SQL）
DECLARE @orderSQL nvarchar(max) --尾部的排序SQL
DECLARE @totalSQL nvarchar(max) --查询总记录数SQL（去掉头部的select）
DECLARE @xml xml --查询条件的XML
DECLARE @outSidnvarchar(100)
DECLARE @beginTime nvarchar(100)
DECLARE @endTime nvarchar(100)
BEGIN
BEGIN TRY -- 异常捕获
--必须放在最前面，紧接着事务点
set @xml = SUBSTRING(@conditionXml,39,LEN(@conditionXml)-38);
select @outSid = c.doc.value('outSid[1]','varchar(100)'),
@beginTime = c.doc.value('beginTime[1]','varchar(100)'),
@endTime = c.doc.value('endTime[1]','varchar(100)')
from @xml.nodes('/nodes/node') c(doc);
SET @beforeSQL = 'wb.OUT_SID, wb.COMPANY_NAME, wb.DELIVER_TIME'
SET @midSQL = ' FROM WAYBILL wb WHERE 1 = 1'
--************************************ 查询条件处理start ************************************--
IF ISNULL(@outSid, '') <> ''
SET @midSQL = @midSQL + ' and wb.OUT_SID = ''' + @outSid + '''';
IF ISNULL(@beginTime, '') <> ''
SET @midSQL = @midSQL + ' and wb.DELIVER_TIME >= ''' + CONVERT(VARCHAR, CAST(@beginTime AS DATETIME), (23)) + '''';
IF ISNULL(@endTime, '') <> ''
SET @midSQL = @midSQL + ' and wb.DELIVER_TIME < ''' + CONVERT(VARCHAR, CAST(@endTime AS DATETIME) + 1, (23)) + '''';
--************************************ 查询条件处理end ************************************--
SET @orderSQL = ' ORDER BY wb.DELIVER_TIME'
SET @querySQL = @beforeSQL + @midSQL
SET @totalSQL = 'count(*)' + @midSQL
EXEC P_Common_Pagination @currentPage, @pageSize, @querySQL, @orderSQL, @totalSQL, @totalCount output, @newCurrentPage output, @exceptionMsg output;
END TRY
BEGIN CATCH --异常发生后，相应的事务等处理
--异常信息
set @exceptionMsg = '消息 ' + cast(ERROR_NUMBER() as varchar(50)) + '，级别 '
+ cast(ERROR_SEVERITY() as varchar(50)) + '，状态 '
+ cast(ERROR_STATE() as varchar(50)) + '，过程 ' + 'P_ExceptionWaybillPageList'
+ '，第 ' + cast(ERROR_LINE() as varchar(50)) + ' 行'
+ '：' + ERROR_MESSAGE();
--异常日志
INSERT INTO Logger(logType, information)
VALUES('ERROR', @exceptionMsg);
END CATCH
END
