SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[alldolist]

/*

***************************************************************

参数说明:

1.DataTableCode             :表名称,视图

2.DataKeyCode         :主关键字

3.DESCFlag               :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc

4.CurrentPage        :当前页码

5.PageSize           :分页尺寸

6.DataFieldCode      :结果列值

7.SearchCondition             :过滤语句，不带Where

8.Group              :Group语句,不带Group By

9.RecordCount              :RecordCount,0：动态数据 >0 :定量数据

***************************************************************/

(

@DataTableCode varchar(1000),

@DataKeyCode varchar(100),

@DESCFlag varchar(200) = NULL,

@CurrentPageIndex int = 1,

@PageSize int = 20,

@DataFieldCode varchar(1000) = '*',

@SearchCondition varchar(1000) = NULL,

@Group varchar(1000) = NULL,

@RecordCount int = 0

)

AS

---- 关闭计数器 ----

SET NOCOUNT ON

DECLARE @FixCount int

IF @CurrentPageIndex > 0 AND @RecordCount>=0

 BEGIN

 /*默认排序*/

 IF @DESCFlag IS NULL OR @DESCFlag = ''

  SET @DESCFlag = @DataKeyCode

 

 DECLARE @SortTable varchar(100)

 DECLARE @SortName varchar(100)

 DECLARE @strSortColumn varchar(200)

 DECLARE @operator char(2)

 DECLARE @type varchar(100)

 DECLARE @prec int

 

 

 /*设定排序语句.*/

 IF CHARINDEX('DESC',@DESCFlag)>0

  BEGIN

   SET @strSortColumn = REPLACE(@DESCFlag, 'DESC', '')

   SET @operator = '<='

  END

 ELSE

  BEGIN

   IF CHARINDEX('ASC', @DESCFlag) = 0

    SET @strSortColumn = REPLACE(@DESCFlag, 'ASC', '')

   SET @operator = '>='

  END

 

 

 IF CHARINDEX('.', @strSortColumn) > 0

  BEGIN

   SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))

   SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))

  END

 ELSE

  BEGIN

   SET @SortTable = @DataTableCode

   SET @SortName = @strSortColumn

  END

 

 SELECT @type=t.name, @prec=c.prec

 FROM sysobjects o

 JOIN syscolumns c on o.id=c.id

 JOIN systypes t on c.xusertype=t.xusertype

 WHERE o.name = @SortTable AND c.name = @SortName

 

 IF CHARINDEX('char', @type) > 0

    SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

 

 DECLARE @strPageSize varchar(50)

 DECLARE @strStartRow varchar(50)

 DECLARE @strFilter varchar(1000)

 DECLARE @strSimpleFilter varchar(1000)

 DECLARE @strGroup varchar(1000)

 DECLARE @strFixCount varchar(1000)

 

 /*默认当前页*/

 IF @CurrentPageIndex < 1

  SET @CurrentPageIndex = 1

 

 /*设置分页参数.*/

 SET @strPageSize = CAST(@PageSize AS varchar(50))

 SET @strStartRow = CAST(((@CurrentPageIndex - 1)*@PageSize + 1) AS varchar(50))

 

 /*筛选以及分组语句.*/

 IF @SearchCondition IS NOT NULL AND @SearchCondition != ''

  BEGIN

   SET @strFilter = ' WHERE ' + @SearchCondition + ' '

   SET @strSimpleFilter = ' AND ' + @SearchCondition + ' '

   set @strFixCount = 'count(*) AS FixCount from ' + @DataTableCode+' where

 

  END

 ELSE

  BEGIN

   SET @strSimpleFilter = ''

   SET @strFilter = ''

   SET @strFixCount = 'count(*) AS FixCount from ' + @DataTableCode

  

  END

 IF @Group IS NOT NULL AND @Group != ''

  SET @strGroup = ' GROUP BY ' + @Group + ' '

 ELSE

  SET @strGroup = ''

 --是否返回动态纪录

 ----返回固帖数

 IF @RecordCount=0

 BEGIN

 EXEC('SELECT '+ @strFixCount)

 END

 ELSE

 BEGIN

 SET @FixCount=@RecordCount

 SELECT  @FixCount AS FixCount

 END

 /*执行查询语句*/

 EXEC(

 '

 DECLARE @SortColumn ' + @type + '

 SET ROWCOUNT ' + @strStartRow + '

 SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @DataTableCode + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @DESCFlag + '

 SET ROWCOUNT ' + @strPageSize + '

 SELECT ' + @DataFieldCode + ' FROM ' + @DataTableCode + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @DESCFlag + '

 '

 )

 END

ELSE

 BEGIN

  SET @FixCount=-1

  SELECT  @FixCount AS FixCount

 END

---- 打开计数器 ----

SET NOCOUNT ON

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO