--定义临时变量
declare @yymm char(7),@mat_no char(15),@vendor_no char(5),@qc_flag char(1),@qc_no char(12),@tj_qty int,@th_qty int
declare c_vqcstock cursor for --定義光標
SELECT convert(char(4),year(dbo.stock.qc_date))+'-'+case when month(dbo.stock.qc_date)>=10 then convert(char(2),month(dbo.stock.qc_date)) else '0'+convert(char(1),month(dbo.stock.qc_date)) end as yymm,
dbo.stock.mat_no, ven_no,qc_no, qc_flag,
(stk_qty+spare) as tj_qty,
(qc_tqty+qc_tsqty) as th_qty
FROM dbo.stock
where stk_type='A'
open c_vqcstock --打开游标
FETCH NEXT FROM c_vqcstock
INTO @yymm,@mat_no,@vendor_no,@qc_no,@qc_flag,@tj_qty,@th_qty
WHILE @@FETCH_STATUS = 0 --向臨時表中加入資料
begin
insert into @T_vqcstock(yymm,mat_no,ven_no,qc_no,qc_flag,tj_qty,th_qty)
values(@yymm,@mat_no,@vendor_no,@qc_no,@qc_flag,@tj_qty,@th_qty)
FETCH NEXT FROM c_vqcstock
INTO @yymm,@mat_no,@vendor_no, @qc_no,@qc_flag,@tj_qty,@th_qty
end
CLOSE c_vqcstock --關閉与釋放光標
DEALLOCATE c_vqcstock
select * from @T_vqcstock