SqlServer存储过程分页查询

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