存储过程，公用分页浏览查询

USE [BJEIS]

GO

/****** Object:  StoredProcedure [dbo].[DividePageQuery]    Script Date: 03/31/2012 10:10:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================  

-- Author:  <LXH>  

-- Create date: <2012-03-31>  

-- Description: <公用分页浏览查询>  

-- =============================================  

ALTER PROCEDURE [dbo].[DividePageQuery]  

(    

　　@v_querystr VARCHAR(max) --查询字符串,当其是表的时候，表名和条件一起传。tablename@    

　　 ,@v_querytype Int  --查询类型，为表0-Table)，查询语句1-select)    

      ,@v_columnstr  VARCHAR(4000)  --所显示字段值    

　　,@v_pagesize Int  --每页数    

　　,@v_pageindex Int  --第几页    

　　,@v_sortby VARCHAR(4000) --排序字段    

　　,@v_rowcount Int output  --总记录数    

　　--,@v_cur_query OUT SYS_REFCURSOR  --结果记录集  

)

as  

　　BEGIN     SET NOCOUNT ON    

　　declare @v_execSQL VARCHAR(max)=''  --执行SQL语句    

　　declare @v_totalCountStr NVARCHAR(max)='' --获得总记录数的SQL语句    

declare @v_whereStr VARCHAR(max)=''  --条件字符串    

declare @v_in_table VARCHAR(30)=''  --表名    

declare @v_afterQueryStr VARCHAR(max)='' --分页浏览最后运行的字符串    

declare @v_startPos INTEGER  --起始位置    

declare @v_endPos INTEGER  --初始位置    

declare @v_pos int  = 1--判断位置    

declare @V_tstr varchar(max)=''    

declare @tempt table (tstr varchar(max))    

declare @Enter VARCHAR(2)=char(13)     

declare @v_SEPARATOR varchar(20)='@#@#'    

declare @ParmDefinition nvarchar(500)    

DECLARE @max_title int;        

 

--分页起始位置   

set @v_startPos = (@v_pageindex-1)*@v_pagesize+1    

--分页后一位置    

set @v_endPos = @v_pagesize*@v_pageindex    

--假如是表结构    

IF @v_querytype = 0    

begin        

　　insert into @tempt SELECT tstr        

      from dbo.fn_get_division_data(@v_querystr,@v_SEPARATOR)        

　　declare s_cursor CURSOR FOR                    

　　　　　　SELECT tstr FROM @tempt

       open s_cursor       

　　fetch next from s_cursor into @V_tstr       

　　while @@FETCH_STATUS =0       

　　begin           

　　　　IF @v_pos = 1            

　　　　　　set @v_in_table = @v_tstr          

　　　　ELSe IF @v_pos = 2            

　　　　　　set @v_whereStr = @v_tstr            

　　　　　　set @v_pos = @v_pos+1          

　　　　fetch next from s_cursor into @V_tstr      

　　end      

　　close s_cursor      

　　DEALLOCATE s_cursor    

end    

　　--生成动态分页查询语句字符串    

　　set @v_execSQL = 'WITH dpq AS'    

　　+ '('    

　　+ '  SELECT ' + @v_columnstr + ',ROW_NUMBER() OVER(ORDER BY '+ @v_sortby +') AS tid '    

　　+ '  FROM '    

　　+ '  ('    

　　+ (CASE  @v_querytype when 0 then    

　　'   SELECT ' + @v_columnstr+'   FROM '+ @v_in_table +' tb '+ @v_whereStr    

　　WHEN 1 then      

　　　　@v_querystr    

　　else ''    

　　END)

     +  '  ) t'

     + ') '+CHAR(13)    

　　--with视图语句查询集    

　　set @v_afterQueryStr =  'SELECT '+ @v_columnstr    

　　+ ' FROM dpq '    

　　+' WHERE tid BETWEEN '+ convert(char(20),@v_startPos) + '  AND  ' + convert(char(20),@v_endPos)    

　　--得到总记录数    

　　IF @v_querytype = 0      

　　　　set @v_totalCountStr = N'SELECT @max_titleOUT=COUNT(*) FROM ' + @v_in_table + ' ' + @v_whereStr    

　　ELSe IF @v_querytype = 1      

　　　　set @v_totalCountStr = N'SELECT @max_titleOUT=COUNT(*) FROM ('+ @v_querystr +') Tc'

    --将语句合并    

　　set @v_execSQL = @v_execSQL + @v_afterQueryStr    

　　--获得总记录数    

　　--EXECUTE IMMEDIATE @v_totalCountStr INTO @v_rowcount    

　　--将静态语句转为动态绑定变量进行运行

     --p_exec_bindvalstr(@v_execSQL,@v_cur_query)

    SET @ParmDefinition = N'@max_titleOUT int OUTPUT';

    EXECUTE sp_executesql @v_totalCountStr, @ParmDefinition, @max_titleOUT=@max_title OUTPUT;    

　　set @v_rowcount=@max_title    

　　EXEC (@v_execSQL)  

END --p_dividepagequery

 

 

 

 

表值函数（返回公用分页Table表fn_get_division_data）

USE [BJEIS] GO

/****** Object:  UserDefinedFunction [dbo].[fn_get_division_data]    Script Date: 03/31/2012 10:36:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ============================================= 

-- Author:  <LXH> 

-- Create date: <2012-03-31> 

-- Description: <表值函数Return表fn_get_division_data> 

-- ============================================= 

ALTER FUNCTION [dbo].[fn_get_division_data]  

(    

@v_str VARCHAR(4000)    

,@v_sp VARCHAR(4000)  

)  

RETURNs @t_sp_data table

         (

          tstr varchar(4000)

               ) as BEGIN

     declare @v_count        int

     declare @v_in_str       VARCHAR(4000)

     declare @v_sp_len int

     declare @v_cur_pos int

    declare @idx int

 

     set  @v_count = 0

     set  @v_sp_len = 0

    set  @v_cur_pos = 0

   set  @idx = 1

     set @v_sp_len = len(@v_sp)

     set @v_in_str = @v_str

     IF @v_in_str IS NULL

       set @v_count =0

     ELSE

        set @v_count = (len(@v_in_str) - len(REPLACE(@v_in_str, @v_sp,'')))/@v_sp_len

    while @idx!>@v_count

     begin

       set @v_cur_pos =charindex(@v_sp,@v_in_str)

       if @v_cur_pos>0

       begin

          if SUBSTRING(@v_in_str,1,@v_cur_pos-1)<>''

        insert into @t_sp_data values(SUBSTRING(@v_in_str,1,@v_cur_pos-1))

         set @v_in_str = SUBSTRING(@v_in_str,@v_cur_pos+@v_sp_len,len(@v_in_str)-@v_cur_pos-@v_sp_len+1)

       end

       set @idx=@idx+1

     END

     if @v_in_str<>''

       insert into @t_sp_data values(@v_in_str)

     RETURN

   END --fn_get_division_data