2005存储过程分页修改版

最新推荐文章于 2024-11-02 10:22:09 发布

liusen5555

最新推荐文章于 2024-11-02 10:22:09 发布

阅读量487

点赞数

文章标签：存储 sql output

本文链接：https://blog.csdn.net/liusen5555/article/details/5316516

版权

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Pager_Pagination]
    @SQL Nvarchar(max), --SQL语句不包括排序
    @CurPage int,    --当前页
    @PageRows int,    --页面尺寸
    @Order Nvarchar(100), --排序字段
    @OrderType Nvarchar(10) --排序类型倒序desc或正序asc
AS
BEGIN
    SET NOCOUNT ON;
    declare @ExceSQL nvarchar(4000)
declare @TotalRecorder int
    --设置开始行号
    declare @start_row_num AS int
    declare @end_row_num AS int
if(@CurPage=1)
   begin
   SET @start_row_num = (@CurPage - 1) * @PageRows
   SET @end_row_num = @start_row_num+@PageRows
   end
else
   begin
   SET @start_row_num = ((@CurPage - 1) * @PageRows)+1
   SET @end_row_num = (@start_row_num+@PageRows)-1
   end
    --设置表示
    declare @RowNumber nvarchar(500)
    set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order +' '+ @OrderType + ') as RowNumber '
    set @SQL = STUFF(@SQL,CHARINDEX ('from', @SQL)-1,1,@RowNumber)
--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,@end_row_num)
    execute(@ExceSQL)
select @TotalRecorder as TotalRecorder
END