------------------------------------------------------------------------- -- 名称(Name)：GetRecordFromTableWithSimpleJoinByPage -- 功能(Function)：分页获得多表连接查询的数据 -- 书写者(Author)： -- 日期(Date)：2006年02月15日 -- 输入参数： /* @tblName nvarchar(255), -- 表名 @priKeyName nvarchar(50), -- 主键列 @fldNames nvarchar(1000), -- 要取值的字段名,多个字段通过逗号分割 @PageSize int = 0, -- 页尺寸，0表示不需要分页 @PageIndex int = 1, -- 页码，从1开始 @OrderType nvarchar(200) = '', -- 设置排序，'':没有排序要求 0：主键升序 1：主键降序 else：用户自定义排序规则 @strWhere nvarchar(2000) = '', -- 查询条件 (注意: 不要加 where) @strJoin nvarchar(1000) = '', -- 连接表 */ --输出参数: -- @TotalItem int output, -- 总的记录数 -- @TotalPage int output -- 总的页数 -- 说明: -- (1) 当多个表简单的连接时,可以使用该存储过程 -- (2) 需要注意的是多个表参与查询时,要查询的字段最好附带表名, -- (3) 排序规则要建立在主表上 -- (4) 适用于使用左外连接,这样连接的表不参与建立排序 ------------------------------------------------------------------------- -- 修改者： -- 修改日期： -- 修改说明： ------------------------------------------------------------------------- ALTER PROCEDURE [dbo].[GetRecordFromTableWithSimpleLeftOuterJoinByPage] @tblName nvarchar(255), -- 表名 @priKeyName nvarchar(50), -- 主键列或标示列 @fldNames nvarchar(1000), -- 字段名,多个字段通过逗号分割 @totalflds nvarchar(500), @PageSize int, -- 页尺寸 @PageIndex int, -- 页码 @OrderType nvarchar(200), -- 设置排序，'':没有排序要求 0：主键升序 1：主键降序 字符串：用户自定义排序规则 @strWhere nvarchar(2000), -- 查询条件 (注意: 不要加 where) @strJoin nvarchar(1000), -- 连接表 @TotalItem int output, @TotalPage int output AS declare @strByPage nvarchar(4000) -- 分页查询语句 declare @strNoPage nvarchar(1500) -- 不分页查询语句，获得所有符合条件的记录 declare @strTotal nvarchar(1000) -- 统计符合条件的纪录数 declare @strNonResult nvarchar(1500) -- 返回空记录的语句 declare @strTmp nvarchar(100) declare @strOrder nvarchar(200) declare @strSql nvarchar(4000) if @OrderType is null or @OrderType='' begin set @strOrder = '' set @strTmp='' end else if @OrderType = '0' -- 降序 begin set @strTmp = @tblName + '.' + @priKeyName + '>(select max([' + @priKeyName + ']) from ' set @strOrder = ' order by ' + @tblName + '.' + @priKeyName + ' asc' end else if @OrderType = '1' -- 降序 begin set @strTmp = @tblName + '.' + @priKeyName + '<(select min([' + @priKeyName + ']) from ' set @strOrder = ' order by ' + @tblName + '.' + @priKeyName + ' desc' end else -- 用户自定义排序规则 begin set @strTmp = '' set @strOrder = ' order by ' + @OrderType end set @strJoin = ' ' + @strJoin + ' ' set @strNonResult = 'select ' + @fldNames + ' from ' + @tblName + @strJoin + ' where 1=2' if @strWhere is null or @strWhere = '' -- 如果没有额外的查询条件 begin set @strTotal = N'select @TotalItem = count(*) from ' + @tblname + @strJoin set @strNoPage = N'select ' + @fldNames + ' from ' + @tblName + @strJoin + @strOrder end else begin set @strTotal = N'select @TotalItem = count(*) from ' + @tblname + @strJoin + ' where ' + @strWhere set @strNoPage = N'select ' + @fldNames + ' from ' + @tblName + @strJoin + ' where ' + @strWhere + @strOrder end -- 取得所有符合查询条件的记录数 --print @strTotal exec sp_executeSql @strTotal,N'@TotalItem int output',@TotalItem output -- 如果没有适合条件的记录时,提供一个空的记录集并退出查询 if @TotalItem = 0 begin set @TotalPage = 0 set @strSql = @strNonResult --exec sp_executeSql @strNonResult end else begin -- 执行查询，此时记录集不为空 if @PageSize = 0 -- 当不需要分页时 begin --set nocount off set @TotalPage = 0 set @strSql = @strNoPage --exec sp_executeSql @strNoPage end else -- 当需要分页时 begin --set nocount off -- 得到记录的页数，并调整页号，分页从1开始 set @TotalPage=CEILING(cast(@TotalItem as float)/@PageSize) if(@PageIndex>@TotalPage) set @PageIndex=@TotalPage if(@PageIndex <1) set @PageIndex=1 if @PageIndex =1 -- 如果是第一页 begin if @strWhere='' begin set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldNames + ' from ' + @tblName + @strJoin + @strOrder end else begin set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldNames + ' from ' + @tblName + @strJoin + ' where ' + @strWhere + @strOrder end end else -- 以后页 begin if (@OrderType='0' or @OrderType='1') -- 按主键升序或降序 begin if @strWhere='' set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldnames + ' from ' + @tblName + @strJoin + ' where ' + @strTmp + ' (select top ' + cast((@PageIndex-1) * @PageSize as varchar) + ' ' + @priKeyName + ' from ' + @tblName + @strJoin + @strOrder + ') as tmptbl)' + @strOrder else set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldnames + ' from ' + @tblName + @strJoin + ' where ' + @strTmp + ' (select top '+ cast((@PageIndex-1) * @PageSize as varchar) + ' ' + @priKeyName + ' from ' + @tblName + @strJoin + ' where ' + @strWhere + @strOrder + ') as tmptbl)' + ' and ' + @strWhere + @strOrder end else -- 没有排序规则或者用户自定义规则 begin if @strWhere='' set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldnames + ' from ' + @tblName + @strJoin + ' where not exists (select * from ' + ' (select top ' + cast((@PageIndex-1) * @PageSize as varchar) + @tblName + '.'+ @prikeyName + ' from ' + @tblName + @strJoin + @strorder + ') as tmpTable ' + ' where tmpTable.' + @priKeyName + ' = ' + @tblName +'.' + @priKeyName + ')' + @strorder else set @strByPage = N'select top ' + cast(@PageSize as varchar) + ' ' + @fldnames + ' from ' + @tblName + @strJoin + ' where not exists (select * from ' + ' (select top ' + cast((@PageIndex-1) * @PageSize as varchar) + @tblName + '.'+ @prikeyName + ' from ' + @tblName + @strJoin + ' where ' + @strWhere + @strorder + ') as tmpTable ' + ' where tmpTable.' + @priKeyName + ' = ' + @tblName + '.' + @priKeyName + ')' + ' and ' + @strWhere + @strorder end end end set @strSql = @strByPage -- exec sp_executeSql @strByPage end --print @strSql exec sp_executeSql @strSql if @totalflds is not null and @totalflds !='' begin declare @strTotalfld nvarchar(2000) if (@strWhere is null or @strWhere='') begin set @strTotalfld = 'select ' + @totalflds + ' from ' + @tblName end else begin set @strTotalfld = 'select ' + @totalflds + ' from ' + @tblName + ' where ' + @strWhere end exec sp_executeSql @strTotalfld end return