SQL编程例2

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER  PROCEDURE [dbo].[sp_Generate_Part_By_Flower_No]
(
  @form_no int
)
as
begin
BEGIN TRANSACTION Part_BOM;
--料號給號後執行
---Generate Part

declare @result int
declare @Part_no nvarchar(20)

select @Part_no=part_no from PartsApplyMain where flower_no=@form_no;

Select @result=count(*) from part
where part_no=@Part_no;

if @result=0
  BEGIN
 insert into part(FlowER_form_no,FlowER_form_ID,part_no,part_description,
 apply_emp_no,part_status,[BU],[Make/Buy/Phantom],unit,[create_BOM_Y/N],mfg_plant,
 english_name,chinese_name,master_category,middle_category,detail_category,
 Model_name,maker_code,Weight,release_date)
 select flower_no as FlowER_form_no,'AUO.FORM.222' as FlowER_form_ID,
 part_no,description as part_description,apply_empno,'RELEASE' as part_status,
 [BU],[make/buy/phantom] as [Make/Buy/Phantom], unit,
 case  upper(create_BOM)
 when 'YES' then 'Y'
 else 'N' end  as [create_BOM_Y/N] ,[dbo].[Remover-](manufacturingplant) as mfg_plant,
 english_name,chinese_name,master_category,middle_category,detail_category,
 model as Model_name,[dbo].[Remover-](maker) as maker_code,weight,getdate() 
 FROM
 (select M.flower_no,M.part_no,M.part_name as description,M.apply_empno,[dbo].[Remover-](field_key) as field_key_as,field_value FROM
 PartsApplyAttr P JOIN PartsApplyMain M on P.doc_no=M.doc_no and M.FORM_STATUS='AP') A
 PIVOT(MAX(field_value) FOR field_key_as in ([BU],[make/buy/phantom],[unit],[create_BOM],[manufacturingplant],[english_name],[chinese_name],[master_category],[middle_category],[detail_category],[model],[maker],[Weight])) P
 where p.flower_no=@form_no

 

 --update Part.part_kind_key  from PartsApplyMain.part_kind_key
 Update Part
 set Part.part_kind_key=PartsApplyMain.part_kind_key
 from Part,PartsApplyMain
 where Part.FlowER_form_no=@form_no
 and Part.FlowER_form_ID='AUO.FORM.222'
 and Part.FlowER_form_no=PartsApplyMain.flower_no
 and Part.part_no=PartsApplyMain.part_no;
 END

COMMIT TRANSACTION Part_BOM;

end