SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Common_GetPagingRecords]
@PageIndex int,
@PageSize int,
@FromClause varchar(512), --单个Table或者用Join关联的多个Table
@SelectFields varchar(4000) = '*', --查询字段，默认为 *
@WhereClause nvarchar(max) = N'', --条件例如"DirectoryID=4"
@SortField varchar(256), --排序字段
@SortFieldIsDesc bit = 1, --正序(0)还是倒序(1)
@ReturnRecordCount bit = 1, --是否需要返回查询到的记录数
@MaxRecords int = -1, --最多获取多少条记录
@TotalRecords int = -1 output, --查询到的记录数
@ReverseOrder bit = 0 output --1表示读取数据的时候 排序要反过来
AS
BEGIN
SET NOCOUNT ON;
if (@PageIndex < 0)
set @PageIndex = 0;
DECLARE @SQLString nvarchar(4000);
DECLARE @WhereString1 nvarchar(4000);
DECLARE @WhereString2 nvarchar(4000);
DECLARE @WhereString3 nvarchar(4000);
IF @WhereClause IS NULL OR @WhereClause = N'' BEGIN
SELECT @WhereString1 = N'';
SELECT @WhereString2 = N'WHERE ';
END
ELSE BEGIN
SELECT @WhereString1 = N'WHERE ' + @WhereClause;
SELECT @WhereString2 = N'WHERE ' + @WhereClause + N' AND ';
END
--1.处理查询记录数
IF @ReturnRecordCount=1
BEGIN
DECLARE @SQLCountString nvarchar(4000);
IF @MaxRecords>0
set @SQLCountString = N'select @TotalRecords = count(*) from (select top ' + str(@MaxRecords)+ N' '+ @SortField
+ N' from ' + @FromClause +N' ' + @WhereString1+ N') as TempCountTable';
ELSE
set @SQLCountString = N'select @TotalRecords = count(*) from ' + @FromClause + N' ' + @WhereString1;
exec sp_executesql @SQLCountString,N'@TotalRecords int out ',@TotalRecords out
if(@PageIndex*@PageSize > @TotalRecords)
set @PageIndex=0
END
else
set @TotalRecords = -1
IF (@MaxRecords>0 and @PageIndex*@PageSize>@MaxRecords)
set @PageIndex=0
IF @PageIndex = 0 BEGIN
SELECT @SQLString = N'SELECT TOP ' + STR(@PageSize)
+ N' ' + @SelectFields + N' FROM ' + @FromClause + N' ' + @WhereString1 + ' ORDER BY ' + @SortField;
IF @SortFieldIsDesc = 1
SELECT @SQLString = @SQLString + ' DESC';
SET @ReverseOrder=0
END
ELSE BEGIN
-----------------------------------------------
DECLARE @SortField_NoPrefix varchar(256);
IF (CHARINDEX('.',@SortField)>0)
SET @SortField_NoPrefix = SUBSTRING(@SortField, CHARINDEX('.',@SortField)+1, LEN(@SortField));
ELSE
SET @SortField_NoPrefix = @SortField;
SET @SQLString='';
DECLARE @GetFromLast BIT
IF @TotalRecords=-1
SET @GetFromLast=0
ELSE BEGIN
DECLARE @TotalPage INT,@ResidualCount INT
SET @ResidualCount=@TotalRecords%@PageSize
IF @ResidualCount=0
SET @TotalPage=@TotalRecords/@PageSize
ELSE
SET @TotalPage=@TotalRecords/@PageSize+1
IF @PageIndex>@TotalPage/2 --页数过半，则从后往前算
SET @GetFromLast=1
ELSE
SET @GetFromLast=0
IF @GetFromLast=1 BEGIN
IF @PageIndex=@TotalPage-1 BEGIN
IF @ResidualCount=0
SET @ResidualCount=@PageSize;
SELECT @SQLString = N'SELECT top ' + STR(@ResidualCount)
+ N' ' + @SelectFields
+ N' FROM ' + @FromClause + N' ' + @WhereString1 + N' ORDER BY ' + @SortField;
IF @SortFieldIsDesc = 0--正序
SELECT @SQLString = @SQLString + ' DESC';
SET @ReverseOrder=1
END
ELSE IF @PageIndex>@TotalPage-1 BEGIN --已经超过最大页数
SELECT @SQLString = N'SELECT ' + @SelectFields
+ N' FROM ' + @FromClause + ' WHERE 0=1'
SET @ReverseOrder=0
END
ELSE BEGIN
SET @PageIndex=@TotalPage-(@PageIndex+1)
IF @SortFieldIsDesc=1
SET @SortFieldIsDesc=0
ELSE
SET @SortFieldIsDesc=1
SET @ReverseOrder=1
END
END
ELSE
SET @ReverseOrder=0
END
IF @SQLString='' BEGIN
DECLARE @TopCount INT
IF @GetFromLast=1 BEGIN
SET @TopCount=@PageSize * (@PageIndex-1)+@ResidualCount
IF @TopCount = 0
SET @TopCount = @PageSize;
END
ELSE
SET @TopCount=@PageSize * @PageIndex
IF @SortFieldIsDesc = 1
SELECT @SQLString = 'SELECT TOP ' + STR(@PageSize)
+ N' ' + @SelectFields
+ N' FROM ' + @FromClause+N' ' + @WhereString2 + @SortField + ' <=
(SELECT Min(' + @SortField_NoPrefix + ') FROM
(SELECT TOP ' + STR(@TopCount) + ' ' + @SortField + ' FROM ' + @FromClause + N' ' + @WhereString1 + '
ORDER BY ' + @SortField + ' DESC) AS TempTable)
ORDER BY ' + @SortField + ' DESC';
ELSE
SELECT @SQLString = 'SELECT TOP ' + STR(@PageSize)
+ N' ' + @SelectFields
+ N' FROM ' + @FromClause + N' ' + @WhereString2 + @SortField + ' >=
(SELECT Max(' + @SortField_NoPrefix + ') FROM
(SELECT TOP ' + STR(@TopCount) + ' ' + @SortField + ' FROM ' + @FromClause+ N' ' + @WhereString1 + '
ORDER BY ' + @SortField + ' ASC) AS TempTable)
ORDER BY ' + @SortField;
END
END
EXEC sp_executesql @SQLString;
END
GO
这是一段MSSQL的分页存储过程,现在需要在MYSQL上面用,因为MSSQL不熟,不会翻译.大家帮忙一下!
