今天看啥
热点:

数据库分页存储过程,支持多表联合查询


数据库分页存储过程,支持多表联合查询

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   --返回页总数 

AS
BEGIN
declare   @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', ' @[email protected] ') 
set   @sortStr2   =   REPLACE(@sortStr2, 'asc', ' @[email protected] ') 
set   @sortStr2   =   REPLACE(@sortStr2, ' @[email protected] ', ' asc ') 
set   @sortStr2   =   REPLACE(@sortStr2, ' @[email protected] ', ' 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 = @sortStr
set   @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   '[email protected] + @whereStr
EXEC   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(@[email protected]*(@pageCount-1)) 
		+  '   '+   @tbFields   +   '    FROM   ' 
		+   @tableNames   +   @whereStr   +   @sortStr2 + ' ) AS  a'
        +   @sortStr3 
               
end 
print @sql
EXEC   SP_EXECUTESQL   @sql
END

www.bkjia.comtruehttp://www.bkjia.com/DB2/1004757.htmlTechArticle数据库分页存储过程,支持多表联合查询 数据库分页存储过程,支持多表联合查询 CREATE PROCEDURE [dbo].[getInQuiryAllByPage]@tableNames varchar(5000),...

相关文章

相关搜索: 过程 数据库

帮客评论

视觉看点