当前位置: 代码迷 >> ASP.NET >> 关于SQL存储过程有关问题 给位大腿进啊
  详细解决方案

关于SQL存储过程有关问题 给位大腿进啊

热度:8981   发布时间:2013-02-25 00:00:00.0
关于SQL存储过程问题 给位大腿进啊!

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



------解决方案--------------------------------------------------------
数据库和代码都在一台机器上的时候可以调试存储过程。
  相关解决方案