动态SQL 实践一:

语句一 :

declare @sql varchar(8000)

select @sql = 'select a.spid ,a.spbh,a.spmch' + char(13)

select @sql = @sql + ' ,sum(case when b.hw = ''' + hw + ''' then b.hwshl else 0 end) as [' + huowname+'数量]' + char(13)
       + ' ,sum(case when b.hw = ''' + hw + ''' then b.hwje else 0 end) as [' + huowname+' 金额]' + char(13)
from huoweizl

select @sql = @sql + ' from spkfk a inner join hwsp b on a.spid=b.spid ' + char(13)

select @sql = @sql + ' group by a.spid ,a.spbh,a.spmch'

print @sql  --打印生成语句

--exec(@sql) --执行生成语句

说明:   spkfk   商品库房库 (spid 商品内码 , spbh 商品编号 , spmch 商品名称 )

        hwsp   货位商品    (hw  货位内码 , spid 商品内码 , hwshl  货位数量 , hwje 货位金额)
 
       huoweizl  货位资料 (hw 货位内码 , huowname 货位名称)

         以上语句用于生成 一个货位余额报表格式如下

         商品编号   |    商品名称  |   货位一数量 | 货位一金额| 货位二数量 | 货位2金额 | ........
         000001            商品一            1                        1                       2                       2   

        .
        .
        .

        以上语句在本地测试时无问题,但实际迁移到客户处使用时提示错误 , 经分析该语句在处理 [货位个数] 在 50 个以下时能正常使用,但50个以上时问题就出现了, 对于变量 @sql 的长度8000 , 需要生成的动态语句就超长了，语句发生截断，所以无法正常执行。

 于是决定对该语句进行重构。

 方案一： 使用多个临时变量存储动态语句，然后执行，如 ：exec (@sql1 + @sql2 + @sql3)
 遇到的问题 ： (1) 对于货位统计语句不好划分属于哪个零时变量 (@sql2? / @sql3? )
        (2) 应该定义多少个临时变量最优(货位个数不确定情况)
        (3) 对于多变量的连接后查询，效率？？

 方案二： 使用临时表处理，先生成一个报表框架等，然后填充数据。
 遇到的问题 ： (1)  使用局部临时表 无法很好的控制 多表连接查询时的效率 (50个以上临时表)
        (2)  使用全局临时表 对于多用户同时查询无法很好的执行 (临时表冲突 , 该问题可与客户协商解决)
 
 最终均衡两个方案后,决定采用(全局临时表)方案二:

  
  declare @sql varchar(8000)  --动态语句存储变量
  
  /**************动态生成结果表(报表框架/全局临时表)*********BEGIN***/
  select @sql = ' declare @num decimal(14,2) ' + char(13)
  
  select @sql = @sql + ' set @num = 0 ' + char(13)
  
  select @sql = @sql + 'select a.spid ,a.spbh,a.spmch' + char(13)
  
  select @sql = @sql + ' , @num as [' + hw+'_shl]' + char(13)
         + ' , @num as [' + hw+'_je]' + char(13)
  from huoweizl
  
  select @sql = @sql + ' into ##tmp_sp from spkfk a inner join hwsp b on a.spid=b.spid ' + char(13)
  
  select @sql = @sql + ' group by a.spid ,a.spbh,a.spmch'
  
  exec(@sql)  --执行动态SQL
  
  /**************动态生成结果表*********END***/

  
  /************** 动态更新数据信息********BEGIN*/
  declare @hw char(11)  --货位内码
  
  declare hw_cursor cursor for
  select hw
  from huoweizl
  
  open hw_cursor
  
  fetch NEXT from hw_cursor INTO @hw
   
  while @@FETCH_STATUS = 0
  begin
    set @sql = 'update  a set [' + @hw + '_shl] = b.hwshl ' + char (13)
    set @sql = @sql + ' , [' + @hw + '_je] = b.hwje ' + char (13)
    set @sql = @sql +  ' from ##tmp_sp a  inner join hwsp b  on a.spid=b.spid ' + char (13)
    set @sql = @sql +  ' where hw = ''' + @hw + '''' + char (13)
    exec (@sql)  --执行动态SQL
    fetch NEXT from hw_cursor INTO @hw
  end
  
  close hw_cursor
  deallocate hw_cursor
  
  /************** 动态更新数据信息********END*/
  
  --返回数据集合
  select * from ##tmp_sp
  
  --删除全局临时表
  drop table ##tmp_sp