資料庫分頁預存程序，支援多表聯集查詢，分頁預存程序

資料庫分頁預存程序，支援多表聯集查詢，分頁預存程序

資料庫分頁預存程序，支援多表聯集查詢

CREATE PROCEDURE [dbo].[getInQuiryAllByPage]@tableNames   varchar(5000),   --表名,可多表,逗號分隔 @tbFields   varchar(5000)= '* ',--欄位名,如果多表請帶首碼 @conditionStr   varchar(5000)= ' ',   --where子句,可為空白,不帶where @sortedStr   varchar(5000),   --排序欄位,可多個,要帶desc或asc,不帶order   by,必須,不可為空 --排序欄位不能有空值,或者在where中排除空值或者用isnull函數解決 @needCount   bit   =   1,   --是否需要得到紀錄總數 @pageIndex   int   =0,   --頁索引 @pageSize   int=10,   --頁大小 @recordCount   int   =0   output,   --返回紀錄總數 @pageCount   int   =0   output   --返回頁總數 ASBEGINdeclare   @sql   nvarchar(4000)   --主sql語句 declare   @sortStr2   varchar(8000)   --order   by子句 declare   @sortStr3   varchar(8000)   --order   by子句 declare @whereStr nvarchar(4000) --條件declare @sortStr nvarchar(4000) --條件set   @sortStr   =   LOWER(@sortedStr) set   @sortStr2   =   REPLACE(@sortStr, 'desc', ' @a@ ') set   @sortStr2   =   REPLACE(@sortStr2, 'asc', ' @d@ ') set   @sortStr2   =   REPLACE(@sortStr2, ' @a@ ', ' asc ') set   @sortStr2   =   REPLACE(@sortStr2, ' @d@ ', ' desc ') --------------------------------------------------------------set   @sortStr2 = REPLACE(@sortStr2,'a.','  ')set   @sortStr2 = REPLACE(@sortStr2,'b.','  ')set   @sortStr2 = REPLACE(@sortStr2,'c.','  ')set   @sortStr2 = REPLACE(@sortStr2,'e.','  ')set   @sortStr2 = REPLACE(@sortStr2,'f.','  ')set   @sortStr2 = REPLACE(@sortStr2,'g.','  ')set   @sortStr3 = @sortStrset   @sortStr3 = REPLACE(@sortStr3,'a.','  ')set   @sortStr3 = REPLACE(@sortStr3,'b.','  ')set   @sortStr3 = REPLACE(@sortStr3,'c.','  ')set   @sortStr3 = REPLACE(@sortStr3,'e.','  ')set   @sortStr3 = REPLACE(@sortStr3,'f.','  ')set   @sortStr3 = REPLACE(@sortStr3,'g.','  ')--------------------------------------------------------------set   @sortStr   =   ' order by '   +   @sortStr set   @sortStr2   =  ' order by '   +   @sortStr2 set   @sortStr3   =  ' order by '   +   @sortStr3 if(@conditionStr   is   not   null   and   @conditionStr   != ' '   ) set   @whereStr   =   '   where   '   +   @conditionStr else set   @whereStr   =   ' ' --if(@needCound   !=   0  or   @pageIndex   =   0 )   --以下獲得紀錄總數   begin DECLARE   @R   int SET   @sql=  'select   @R=count(*)   from   '+@tableNames + @whereStrEXEC   SP_EXECUTESQL   @SQL,N'@R   int   OUTPUT ',@R   OUTPUT SET   @recordCount   =  @R set   @pageCount   =   ((@recordCount-1)/@pageSize)+1   end if(@pageIndex <2)   --如果是第一頁 begin set   @pageIndex   =   1 set   @sql= 'select   top   '+   str(@pageSize)         +   '   '+   @tbFields   +   '   from   '   +   @tableNames         +   @whereStr   +   @sortStr; end else   --其它頁 begin    if( @recordCount>@pageIndex*@pageSize)  SET   @sql=   'SELECT   *   FROM   ( ' +   'SELECT   TOP   '   +   STR(@pageSize)   +   '   *   FROM   ( ' +   'select   top   '   +   STR(@pageSize*@pageIndex)   +   '   '+   @tbFields   +   '   FROM   ' +   @tableNames   +   @whereStr   +   @sortStr   +   ')   as   a ' +   @sortStr2   +   ')   as   b '   +   @sortStr3          else    SET   @sql=   ' select  *   FROM   ('         + ' SELECT   top '+  STR(@recordCount-@pageSize*(@pageCount-1)) +  '   '+   @tbFields   +   '    FROM   ' +   @tableNames   +   @whereStr   +   @sortStr2 + ' ) AS  a'        +   @sortStr3                end print @sqlEXEC   SP_EXECUTESQL   @sqlEND