语句一 :
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
|