SQLSERVER 通用分页存储过程

 

 

调用通用分页存储过程

ALTER PROCEDURE [dbo].[EnterpriseInfo_By_OrganNo_Page] 

    -- Add the parameters for the stored procedure here

     @OrganNo       NVARCHAR(20) = NULL,  -- 组?织?机?构?编?号?                  

     @RoleID        int,      --id

     @beginDateTime datetime,

     @PageSize       int ,    --每?页?显?示?的?记?录?个?数?

     @pageIndex    int     , ----要?显?示?那?一?页?的?记?录?

     @RecordCount int=0 OUTPUT,      ----查?询?到?的?总?记?录?数?

     @pageCount INT OUTPUT,  ----查?询?结?果?分?页?后?的?总?页?数?

     @strSQL nvarchar(max)='' output

AS

BEGIN

    DECLARE @_OrganNo NVARCHAR(20)

    Declare @_strWher   nvarchar(max)

    declare @_UserTime int

--处?理?SQL查?询?

IF @OrganNo IS NOT NULL AND @OrganNo <> ''

    SET @_OrganNo = @OrganNo

--企?业?信?息?

Select o.Name,o.OrganComCode,o.ComRegNo,o.Corporation

From TDOrgan o

Where o. OrganNo=@_OrganNo

--企?业?联?系?人?信?息?

--Select l.Name,l.Phone,l.Mobile,l.Email,l.Address,l.ZipCode

--From TDGrpLinkMan l

--Where l.OrganNo=@_OrganNo

set @_strWher='OrganNo='''+@_OrganNo+''''

if @RoleID is not null

begin

  @_strWher=@_strWher+' and RoleID='+CONVERT(nvarchar(50), @RoleID)

end

if @beginDateTime is not null

begin

  @_strWher=@_strWher+' and beginDateTime='''+CONVERT(nvarchar(50), @beginDateTime,23)+''''

end

exec commPageList

   @tblName='TDGrpLinkMan',

   @fldName='Name,Phone,Mobile,Email,Address,ZipCode,Fax',

   @pageSize=@PageSize,

   @pageIndex=@pageIndex,

   @fldSort='Name',

   @strCondition=@_strWher,   @strGroupBy=null,

   @pageCount=@pageCount OUTPUT,

   @RecordCount=@RecordCount  OUTPUT,

   @UsedTime=@_UserTime OUTPUT,

   @strSql=@strSQL output

END

 

 

通用分页存储过程

--参数说明
-------------------------------------------------------------
/*
* @tblName   ----要显示的表或多个表的连接
* @fldName   ----要查询出的字段列表,*表示全部字段
* @pageSize   ----每页显示的记录个数
* @pageIndex  ----要显示那一页的记录
* @fldSort   ----排序字段列表或条件，如：id desc (多个id desc,dt asc)
* @strCondition  ----查询条件,不需where
* @strGroupBy   ----分组条件
* @pageCount  ----查询结果分页后的总页数
* @RecordCount  ----查询到的总记录数
* @UsedTime   ----耗时测试时间差
* @strSql   ----最后返回的SQL语句
*/
ALTER PROCEDURE [dbo].[commPageList]
(
 @tblName NVARCHAR(MAX),
 @fldName NVARCHAR(MAX),
 @pageSize INT,
 @pageIndex INT,
 @fldSort NVARCHAR(MAX),
 @strCondition NVARCHAR(MAX),
 @strGroupBy nvarchar(max),
 @pageCount INT=0 OUTPUT,
 @RecordCount INT OUTPUT,
 @UsedTime INT=0 OUTPUT,
 @strSql nvarchar(max)='' OUTPUT
)
AS
 SET NOCOUNT ON
 DECLARE @startRow int, @endRow INT     ----用于存放起始值与结束值
 DECLARE @timediff DATETIME       ----用于存放时间差值
 DECLARE @SqlCounts NVARCHAR(MAX)     ----用于存放总记录数查询语句
 DECLARE @strTmp NVARCHAR(MAX)      ----用于存放查询语句
 DECLARE @strWhere NVARCHAR(MAX)      ----用于存放查询条件
 SELECT @timediff = getdate()      ----设置开始时间
 SET @startRow = (@pageIndex - 1) * @pageSize + 1 ----设置起始值
 SET @endRow = @startRow + @pageSize - 1    ----设置结束值
 ----设置总记录数查询语句----
 IF @strCondition is null or @strCondition='' ----没有设置查询条件
  BEGIN
   SET @SqlCounts = 'SELECT @RecordCount = COUNT(*) FROM ' + @tblName
  END
 ELSE ----有设置查询条件
  BEGIN
   SET @strWhere = ' WHERE ' + @strCondition
   SET @SqlCounts = 'SELECT @RecordCount = COUNT(*) FROM ' + @tblName + @strWhere
  END  
 ----取得查询结果总数量-----
 exec sp_executesql @SqlCounts,N'@RecordCount int out ',@RecordCount out 
 declare @tmpCounts int 
 if @RecordCount = 0 
  set @tmpCounts = 1 
 else 
  set @tmpCounts = @RecordCount 
    ----取得分页总数----
    set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
 ----开始分页计算并取出相应数据----
 ----SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY contnet_keyword_addDate DESC) AS RowNumber FROM Public_content_keyword ) T WHERE T.RowNumber BETWEEN @startRow AND @endRow
  if @strGroupBy is not null and @strGroupBy <>'' ----有设置gruop by条件
       begin
        set @tblName=@tblName + ' group by '+@strGroupBy
       end
    
  
 IF @pageIndex = 1    
  BEGIN
   IF @strCondition is null or @strCondition='' ----没有设置显示条件
    BEGIN
     SET @strTmp = 'SELECT TOP ' + CAST(@pageSize as VARCHAR(max)) + ' ' + @fldName + ' FROM ' + @tblName + ' order by '+ @fldSort
    END
   ELSE ----有设置查询条件
    BEGIN
     SET @strTmp = 'SELECT TOP ' + CAST(@pageSize as VARCHAR(max)) + ' ' + @fldName + ' FROM ' + @tblName + @strWhere +' order by '+ @fldSort
    END
  END
 ELSE
  BEGIN
   IF @strCondition is null or @strCondition='' ----没有设置显示条件
    BEGIN
     --SET @strTmp = 'SELECT ' + @fldName + ' FROM (SELECT ' + @fldName + ',ROW_NUMBER() OVER (ORDER BY ' + @fldSort + ') AS RowNumber FROM '+ @tblName +') T WHERE T.RowNumber BETWEEN ' + CAST(@startRow as VARCHAR(max)) + ' AND ' + CAST(@endRow as VARCHAR(max))
     SET @strTmp = 'SELECT * FROM (SELECT ' + @fldName + ',ROW_NUMBER() OVER (ORDER BY ' + @fldSort + ') AS RowNumber FROM '+ @tblName +') T WHERE T.RowNumber BETWEEN ' + CAST(@startRow as VARCHAR(max)) + ' AND ' + CAST(@endRow as VARCHAR(max))
    END
   ELSE ----有设置查询条件
    BEGIN
     --SET @strTmp = 'SELECT ' + @fldName + ' FROM (SELECT ' + @fldName + ',ROW_NUMBER() OVER (ORDER BY ' + @fldSort + ') AS RowNumber FROM '+ @tblName + @strWhere +') T WHERE T.RowNumber BETWEEN ' + CAST(@startRow as VARCHAR(max)) + ' AND ' + CAST(@endRow as VARCHAR(max))
       SET @strTmp = 'SELECT * FROM (SELECT ' + @fldName + ',ROW_NUMBER() OVER (ORDER BY ' + @fldSort + ') AS RowNumber FROM '+ @tblName + @strWhere +') T WHERE T.RowNumber BETWEEN ' + CAST(@startRow as VARCHAR(max)) + ' AND ' + CAST(@endRow as VARCHAR(max))

    END
  END 
------返回查询结果-----
SET @strSql = @strTmp
EXEC(@strTmp)
SET @UsedTime = DATEDIFF(ms,@timediff,GETDATE())
SET NOCOUNT OFF