1 CREATE PROCEDURE [dbo].[ITMRECOMH_Insert]
 2 @ITMREF VarChar(20),
 3 @STA VarChar(1),
 4 @CRETIME VarChar(20),
 5 @CREUSE VarChar(20),
 6 @ITMRECOMD_XML Xml,
 7 @Msg varchar(2000) output,
 8 @ID Int output
 9 AS
10 BEGIN
11     SET NOCOUNT ON;
12     begin try
13     Begin Tran--事务开始
14         --插入主表信息
15          INSERT INTO ITMRECOMH 
16              ([ITMREF],[STA],[CRETIME],[CREUSE]) 
17         VALUES (@ITMREF,@STA,@CRETIME,@CREUSE) 
18         SELECT @ID=@@identity;
19 
20         --将子表XML保存到临时表
21         if object_id('tempdb..#ITMRECOMD_T') is not null
22             drop table  #ITMRECOMD_T
23         select * into #ITMRECOMD_T from (
24             select  
25                 --产品推荐子表
26                 T.C.value('ID[1]','Int') as [ID],
27                 --行号
28                 T.C.value('LIN[1]','Int') as [LIN],
29                 --产品编号
30                 T.C.value('ITMREF[1]','VarChar(20)') as [ITMREF]
31         from @ITMRECOMD_XML.nodes('//ITMRECOMDXML') as T(C) )  L 
32         --插入子表
33         Insert into ITMRECOMD ([lin],[HITMREF],[ITMREF])
34         SELECT  [lin],@ITMREF,[ITMREF] 
35         FROM #ITMRECOMD_T
36         --删除临时表
37         drop table  #ITMRECOMD_T
38     Commit Tran--事务结束
39     return 0    
40 end try
41     begin Catch
42         select @Msg='错误消息:'+Error_Message()+'错误号:'+Rtrim(cast(ERROR_NUMBER() as char))+';行号:'+Rtrim(cast(Error_Line() as char))+';存储过程:'+Error_Procedure()
43         if @@TranCount>0
44             Rollback Tran
45         Return 999
46     end Catch
47 END