后台临时表处理前端第三方控件不能处理的问题

有时候在前端利用第三方控件不能很好处理的事情，或许的后台利用临时表替代前端控件所产生的临时数据集。如下是近段时间碰到的现象，部分记录如下：

/*******************************************      
 * Author  : lxh      
 * Date  : 2018-01-26      
 * Description : 制板单款号分析报表(设计部)     
 *     
 *  exec  uP_BanAnalysisReport    
    '''2018冬季''',null,'''休闲一''','''女装''',null,   null,null,null,null,null,    
    null,null,null,null,null,  null,null,null,null,null,    
    null,null  
 * modify:增加临时表处理桌面AllData信息20180227  
 *******************************************/                           
alter PROC [dbo].[uP_BanAnalysisReport]                                                                                                              
   @Season    VARCHAR(30),      
   @responsible   VARCHAR(30),      
   @series    VARCHAR(30),      
   @sex     VARCHAR(30),      
   @goods_category  VARCHAR(30),      
   @Styleno    VARCHAR(30),      
   @stylenametype  VARCHAR(30),      
   @styletype   VARCHAR(30),      
   @styletype2   VARCHAR(30),      
   @fabname    VARCHAR(30),      
--No.10      
   @fabcharacter  VARCHAR(30),      
   @Design_style  VARCHAR(30),      
   @thickness   VARCHAR(30),      
   @Collartype   VARCHAR(30),      
   @Waist_type   VARCHAR(30),      
   @BX_length   VARCHAR(30),      
   @Pants_type   VARCHAR(30),      
   @outsidesleeve  VARCHAR(30),      
   @UnitpriceFrom  INT,      
   @UnitpriceTo   INT,      
--No.20      
   @color    VARCHAR(30),      
   @loginid    VARCHAR(20)                                                                                          
as                                                                                                              
                                                                                  
begin                                                      
      declare @sql varchar(8000),@charenter varchar(10)                                                                                               
      set @charenter=char(13)+char(10)                               
                            
                                                                                                                                                        
  set @SQL = ''                                                                                                       
                                                         
  set @SQL = @SQL+' 
 IF EXISTS(SELECT 1 FROM sysobjects WHERE id = OBJECT_ID(N''[AllData]'') AND xtype=''U'' ) DROP TABLE AllData]        
 SELECT DISTINCT CASE WHEN SUBSTRING(REPLACE(a.salesSeason,'' '',''''),1,4)<''2019'' THEN a.styleno ELSE h2.styleno2 END styleno      
   ,m.responsible    --设计部总负债人      
   ,a.salesSeason    --季度      
   ,h2.goods_category   --商品类别      
         
   ,a.styletype    --款式类型      
   ,a.styletype2    --类型      
   ,a.stylenametype   --款式大类      
   ,a.fabname     --面料名称      
         
   ,a.fabcharacter   --面料特性      
   ,a.thickness    --厚度      
   ,a.Collartype    --领型      
   ,a.Design_style   --款式      
   ,a.Pants_type    --袖型/裤型      
   ,a.Waist_type    --腰型      
   ,a.BX_length    --长度/衫长      
   ,a.outsidesleeve   --袖长      
   ,a.Unitprice    --建议价      
         
   ,a.series     --品牌      
   ,a.sex      --性别      
     
   ,a.banxing   --板型20180131_add  
                     
   ,'''' AS color    --颜色,暂时置空,后续再关联新表   
  into AllData--20180227_add                  
  FROM dsg_bi.dbo.Ban_Lastver_MBT07 b(NOLOCK) LEFT JOIN    
    ban_makebill_head a(NOLOCK) ON a.mb_number=b.mb_number LEFT JOIN        
    ban_makebill_head2 h2(NOLOCK) ON h2.mb_number=a.mb_number LEFT JOIN       
    MC_dsgdept m(NOLOCK) ON a.dsgdeptname=m.dsgdeptname          
 WHERE 1=1                                                                                                              
  ' + @charenter                                                                                                             
                                                                          
  IF @Season IS NOT NULL SET @sql=@sql+'      
 and a.salesSeason like ' + @Season + @charenter      
  IF @responsible IS NOT NULL SET @sql=@sql+'      
 and m.responsible like ' + @responsible + @charenter      
  IF @series IS NOT NULL SET @sql=@sql+'      
 and a.series like ' + @series + @charenter      
  IF @sex IS NOT NULL SET @sql=@sql+'      
 and a.sex like '+ @sex + @charenter      
  IF @goods_category IS NOT NULL SET @sql=@sql+'      
    and h2.goods_category like ' + @goods_category + @charenter                                                               
  IF @Styleno IS NOT NULL SET @sql=@sql+'      
 and a.Styleno like ' + @Styleno + @charenter       
  IF @stylenametype IS NOT NULL SET @sql=@sql+'      
 and a.stylenametype like ' + @stylenametype + @charenter        
  IF @styletype IS NOT NULL SET @sql=@sql+'      
 and a.styletype like ' + @styletype + @charenter       
  IF @styletype2 IS NOT NULL SET @sql=@sql+'      
 and a.styletype2 like ' + @styletype2 + @charenter         
  IF @fabname IS NOT NULL SET @sql=@sql+'      
 and a.fabname like ' + @fabname + @charenter       
  IF @fabcharacter IS NOT NULL SET @sql=@sql+'      
 and a.fabcharacter like ' + @fabcharacter + @charenter        
  IF @Design_style IS NOT NULL SET @sql=@sql+'      
 and a.Design_style like ' + @Design_style + @charenter       
  IF @thickness IS NOT NULL SET @sql=@sql+'      
 and a.thickness like ' + @thickness + @charenter         
  IF @Collartype IS NOT NULL SET @sql=@sql+'      
 and a.Collartype like ' + @Collartype + @charenter       
  IF @Waist_type IS NOT NULL SET @sql=@sql+'      
 and a.Waist_type like ' + @Waist_type + @charenter       
  IF @BX_length IS NOT NULL SET @sql=@sql+'      
 and a.BX_length like ' + @BX_length + @charenter        
  IF @Pants_type IS NOT NULL SET @sql=@sql+'      
 and a.Pants_type like ' + @Pants_type + @charenter        
  IF @outsidesleeve IS NOT NULL SET @sql=@sql+'      
 and a.outsidesleeve like ' + @outsidesleeve + @charenter       
  IF @UnitpriceFrom IS NOT NULL SET @sql=@sql+'      
 and a.Unitprice >= ' + @UnitpriceFrom + @charenter      
  IF @UnitpriceTo IS NOT NULL SET @sql=@sql+'      
 and a.Unitprice <= ' + @UnitpriceTo + @charenter    
 
 --2018-02-27_add
 SET @sql=@sql + ' select * from AllData' +@charenter
                   
  print @SQL                                                                                                       
  exec(@SQL)                                                                                                    
end 

在前端有些第三方控件，比如有些数据集控件不支持SQL的一些运算符(PIVOT等），如下示例：

SELECT  responsible,Design_style,series,sex,[2001] as '针织',[2002] as '梭织',[2003] as '牛仔'      
,[2004] as '毛织' FROM (   SELECT responsible,Design_style,series,sex    ,case when fabname='针织' then 2001
                                                                           WHEN fabname='梭织' then 2002
                                                                           WHEN fabname='牛仔' then 2003
                                                                           WHEN fabname='毛织' then 2004 end as fabname    FROM AllData   
WHERE stylenametype LIKE '%外套类%'  ) vip PIVOT  (   COUNT(fabname) FOR fabname IN ([2001],[2002],[2003],[2004])   ) AS sFabname  
ORDER BY Design_style

再有些系统数据集（ADO相关控件）支持特殊运算符，但不能处理第三方插件的其他功能，比如上示例中'AllData'是别名而非实体表对象。那就有些功能就不能很好地实现了。此时就可以
利用后台来处理，把查询结果的数据集变为临时表(诸如AllData是实体表)来处理。