CREATE PROCEDURE [dbo].[USP_GetFrontDataList_ByFullIndex]
(
@TableViewQueryName  Varchar( 1024 ),
   --Table或View或者Query的名字或字符串 
@Key    Varchar( 50 ),  --关键字 
@SelectStr   Varchar( 500 ),   --选择列字符串 
@Criteria   Varchar( 8000 ),--查询条件 
@Sort    Varchar( 255 ), --排序字符串 
@FristTopNum   INT,      --显示的第一页置顶的数目 
@Page    BIGINT  OUTPUT, --显示的当前页号 
@CurrentPageRow  BIGINT,  --页大小(显示多少行)
@TotalCount   BIGINT OUTPUT   --通过该查询条件，返回的查询记录的总页数 
)
AS
SET NOCOUNT ON
 if charindex(';',@Criteria)>0 or charindex('--',@Criteria)>0 or charindex('/*',
@Criteria)>0 or 
charindex('*/',@Criteria)>0 or charindex('syscolumns',@Criteria)>0  or 
charindex('sysfiles',@Criteria)>0
or charindex('char(124)',@Criteria)>0  or charindex('1=1',@Criteria)>0 
RETURN 

DECLARE @TotalStr  nVarchar(4000)
DECLARE @Str   nVarchar(4000)
DECLARE @TopRowNum bigint
IF  @SelectStr IS NULL AND
RTRIM(LTRIM(@Criteria)) = ''
SET @SelectStr = '*'
IF @FristTopNum IS NULL AND @FristTopNum < 0
BEGIN
SET @FristTopNum = 0
END
ELSE IF @FristTopNum > @CurrentPageRow
BEGIN
SET @FristTopNum = @CurrentPageRow
END
IF  @CurrentPageRow > 0
BEGIN
IF @Criteria IS NOT NULL AND
RTRIM(LTRIM(@Criteria)) <> ''
BEGIN
SET  @TotalStr = 'SELECT @TotalCount=COUNT(*) FROM  ' + 
'('+ @TableViewQueryName   +')'+ ' T '
+ ' WHERE ' + @Criteria
END
ELSE
BEGIN
SET  @TotalStr = 'SELECT @TotalCount=COUNT(*) FROM ' +
 '('+@TableViewQueryName  +')'+ ' T ' 
END
PRINT @TotalStr
EXEC  sp_ExecuteSql @TotalStr, N'@TotalCount bigint output',@TotalCount output

SET @TotalCount = @TotalCount + isnull(@FristTopNum  ,0)
DECLARE @TotalPage bigint
SET @TotalPage = @TotalCount/@CurrentPageRow
IF @TotalCount%@CurrentPageRow > 0
BEGIN
SET @TotalPage = @TotalPage + 1
END 
IF @Page <= 0 
BEGIN
SET @Page = 1
END
IF @TotalPage > 0 AND
@Page > @TotalPage
BEGIN
SET @Page = @TotalPage
END 
--组织查询语句 
SET  @Str =  'SELECT   ' +   @SelectStr + ' FROM (' +  
@TableViewQueryName  + ') T WHERE  T.SerialNumber >' +
cast ((@Page-1)  as varchar(10)) + '*' +cast( @CurrentPageRow as varchar(10))+  
' AND T.SerialNumber <= '+
cast (@Page  as varchar(10)) + '*' +cast( @CurrentPageRow as varchar(10))
IF @Sort IS NOT NULL ANDRTRIM(LTRIM(@Sort)) <> ''
BEGIN
IF @Criteria IS NOT NULL AND  RTRIM(LTRIM(@Criteria)) <> ''
BEGIN
SET  @Str = @Str +   ' AND (' + @Criteria + ') ORDER BY '+@Sort 
END
ELSE
BEGIN
SET  @Str = @Str +   ' AND  (' + @Criteria + ') ORDER BY '+@Sort 
END
END
ELSE
BEGIN 

IF @Criteria IS NOT NULL AND   RTRIM(LTRIM(@Criteria)) <> ''
BEGIN
SET @str = @str +  ' AND (' + @Criteria + ')  '   
END

END
--对无记录时当前页数的处理 
IF @TotalCount=0
BEGIN
SET @Page = 0
END
END

EXEC  sp_ExecuteSql @Str