SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=============================================
ALTER Proc [dbo].[MM_Apply_Insert]
@SChildID int ,
@RegisterDate varchar(20) ,
@Curriculum varchar(20) ,
@CreateUser varchar(20) ,
@CreateUserID int ,
@CreateDate varchar(20) ,
@Remark varchar(200) ,
@ClassName varchar(20),
@ClassNameID Int ,
@PayTag int ,
@SchoolStartDate varchar(20) ,
@SchoolEndDate varchar(20) ,
@StudentNumber VarChar(50),
@Field VarChar(50),
@Apply_XML XML,--
@Msg varchar(200) output, --提示信息
@ID int output
as
BEGIN
SET NOCOUNT ON;
Begin Try
Begin Tran
--插入到入库主表
Insert into MM_StudentApply([SChildID],[RegisterDate],[Curriculum],[CreateUser],[CreateUserID],[CreateDate],[Remark],[ClassName],[ClassNameID],[PayTag],[SchoolStartDate],[SchoolEndDate],[StudentNumber],[Field])values(@SChildID,@RegisterDate,@Curriculum,@CreateUser,@CreateUserID,@CreateDate,@Remark,@ClassName,@ClassNameID,@PayTag,@SchoolStartDate,@SchoolEndDate,@StudentNumber,@Field) select @ID=@@identity;
--将子表XML保存到临时表
if object_id('tempdb..#ApplyDetail') is not null
drop table #ApplyDetail
select * into #ApplyDetail from (
select
T.C.value('CreateDate[1]','varchar(20)') as CreateDate, --缴费日期
T.C.value('ApMoney[1]','Decimal(18,2)') as ApMoney, --缴费金额
T.C.value('UserName[1]','varchar(20)') as UserName, --收款人
T.C.value('UserID[1]','int') as UserID, --收款人ID
T.C.value('CreateName[1]','varchar(20)') as CreateName, --记录人
T.C.value('CreateID[1]','int') as CreateID, --记录人ID
T.C.value('Remark[1]','varchar(200)') as Remark, --备注
T.C.value('SchoolMoney[1]','Decimal(18,2)') as SchoolMoney, --学费
T.C.value('OtherMoney[1]','Decimal(18,2)') as OtherMoney, --其它费用
T.C.value('StayMoney[1]','Decimal(18,2)') as StayMoney, --住宿费
T.C.value('MakeType[1]','Int') as MakeType --收款方式
from @Apply_XML.nodes('//ApplyDetail') as T(C) ) L
--修改咨询表的咨询状态
update MM_StudentConsult set [Type]='E' where ID=@SChildID;
--同步把学员信息添加到学生档案
Insert into Rs_StudentInfo([StudentName],[Sex],[Nation],[Province],[City],[Birthday],[Marriage],[Phone],[Email],[QQ],[RgencyLinkPhone],[Diplomas],[SchoolTag],[Specialty],[CodeID],[Remark],[ImageName],[ClassName],[ClassID])
select [Name],Sex,Nation,Province,City,Birthday,ToWork,Phone,Email,QQ,'',Diplomas,'',@Field,'',Remark,'',@ClassName,@ClassNameID from MM_StudentConsult where ID=@SChildID;
-- 插入子表
declare @UID int
select top 1 @UID=UserID from #ApplyDetail
if (@UID > 0)
begin
Insert into MM_StudentApplyChild([ApplyChild],[CreateDate],[ApMoney], [UserName],[UserID],[CreateName],[CreateID],[Remark],[SchoolMoney],[OtherMoney],[StayMoney],[MakeType]) select @ID,CreateDate,ApMoney,UserName,UserID,CreateName,CreateID,Remark,SchoolMoney,OtherMoney,StayMoney,MakeType from #ApplyDetail
end
--删除临时表
drop table #ApplyDetail;
declare @ChildCount int;
select @ChildCount=count(*) from MM_StudentApplyChild where [ApplyChild]=@ID;
if( @ChildCount=0)--子表无插入到数据
begin
set @Msg='子表无数据，或数据不正确';
end
Commit Tran
End Try
Begin Catch
select @Msg='错误消息:'+Error_Message()+'错误号:'+Rtrim(cast(ERROR_NUMBER() as char))+';行号:'+Rtrim(cast(Error_Line() as char))+';存储过程:'+Error_Procedure()
if @@TranCount>0
Rollback Tran
End Catch
end
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
------解决方案--------------------------------------------------------
数据库和代码都在一台机器上的时候可以调试存储过程。