这是代码 create procedure ( @ID bigint, @Province varchar(20), @City varchar(20), @Name varchar(50), @Sex varchar(2), @School varchar(50), @IdentityNumber varchar(50), @Address varchar(200), @DataReturn tinyint OUTPUT ) AS declare @Provincecode tinyint declare @citycode tinyintbegin transactionselect @Provincecode=1 select @citycode=1if not exists (Select * from Provincelist where @ProvinceName=@Province) begin select @provincecode=count(*)+1 from Provincelist insert provincelist values(@provincecode,@Province) end else begin select @provincecode=ProvinceCode from provincelist where ProvinceName=@Province end if @@Error<>0 rollback transaction select @DataReturn=10 return (10) else commit transaction select @DataReturn=20 .................. --类识的插入表操作有5个--最后if @@Error<>0 rollback transaction select @DataReturn=10 return (10) else commit transaction select @DataReturn=20请高手指点 多谢!
if not exists (Select * from Provincelist where @ProvinceName=@Province) ^^这里是不是笔误??begin select @provincecode=count(*)+1 from Provincelist insert provincelist values(@provincecode,@Province) end else begin select @provincecode=ProvinceCode from provincelist where ProvinceName=@Province ^^^整个ELSE的处理没有必要,还是有些代码没有贴出来??end if @@Error<>0 rollback transaction select @DataReturn=10 return (10) else commit transaction select @DataReturn=20
if和else之間為什麼不寫begin end? 還有一些問題,我待會再看.
斑竹及各位,小弟现就将正式的源代码给出,请高人指点, 望斑竹帮忙!小弟吐血拿出源代码,求高人!!!!一定给分!!!!!!create procedure AddOneHigh ( @StreamNumber varchar(15), @IdentityNumber varchar(50), @Name varchar(50), @Sex char(2), @BloodType varchar(4), @Birthday varchar(20), @BornProvince varchar(40), @BornCity varchar(40), @YearLimitation varchar(2), @GraduateSchool varchar(40), @MasterName varchar(50), @SchoolAddress varchar(100), @DegreeLevel varchar(20), @StudyTime varchar(25), @GraduateNumber varchar(50), @GraduateCardName varchar(50), @DeliverDate varchar(20), @Memo varchar(200), @DataReturn tinyint OUTPUT ) AS declare @StreamNumbera bigint declare @provincecode tinyint declare @citycode int declare @schoolcode int declare @studytimecode intbegin transaction--把C++builder中的String转换为bigint型 select @StreamNumbera=convert(bigint,@StreamNumber) --调用存储过程插入分支表并获得其相应的代码值exec @provincecode=InsertProvince @BornProvince=@BornProvince exec @citycode=InsertCity @BornCity=@BornCity exec @schoolcode=InsertSchool @GraduateSchool=@GraduateSchool,@MasterName=@MasterName,@SchoolAddress=@SchoolAddress exec @studytimecode=InsertStudytime @StudyTime=@StudyTimeif @provincecode=0 or @citycode=0 or @schoolcode=0 or @studytimecode=0 begin --插入代码时出错 rollback transaction select @DataReturn=10 return (10) end --插入mainindex表 insert mainindex values(@StreamNumbera,@IdentityNumber,@GraduateNumber) if @@Error<>0 begin raiserror('插入mainindex时,记录时出错!请检查你的--流水号--或--证书编号--有没有与以前的重复!',16,1) rollback transaction select @DataReturn=50 return (50) end --插入证书信息 insert highcardinfo(GraduateNumber,GraduateCardName,DeliverDate) values(@GraduateNumber,@GraduateCardName,@DeliverDate) if @@Error<>0 begin raiserror('插入记录时出错!请检查你的--证书编号--有没有重复!',16,1) rollback transaction select @DataReturn=30 return (30) end--插入个人信息表 insert highpersoninfo values(@StreamNumbera,@IdentityNumber,@Name,@Sex,@BloodType,@Birthday,@provincecode,@citycode,@YearLimitation,@schoolcode,@DegreeLevel,@studytimecode,@GraduateNumber,@Memo) if @@Error<>0 begin raiserror('插入记录时出错!请检查你的--流水号--或--证书编号--有没有重复!',16,1) rollback transaction select @DataReturn=40 return (40) end else begin commit transaction select @DataReturn=20 return (20) end GO
begin transtion.... if @@error<>0 goto quiterror .....if @@error<>0 goto quiterror--取消在中间的提交 在过程末统一提交 commit transaction goto endsave quiterror: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:
上面是笔误,写错了,实际是
if @@Error<>0
rollback transaction
else
commit transaction,
create procedure
(
@ID bigint,
@Province varchar(20),
@City varchar(20),
@Name varchar(50),
@Sex varchar(2),
@School varchar(50),
@IdentityNumber varchar(50),
@Address varchar(200),
@DataReturn tinyint OUTPUT
)
AS
declare @Provincecode tinyint
declare @citycode tinyintbegin transactionselect @Provincecode=1
select @citycode=1if not exists (Select * from Provincelist where @ProvinceName=@Province)
begin
select @provincecode=count(*)+1 from Provincelist
insert provincelist values(@provincecode,@Province)
end
else
begin
select @provincecode=ProvinceCode from provincelist where ProvinceName=@Province
end
if @@Error<>0
rollback transaction
select @DataReturn=10
return (10)
else
commit transaction
select @DataReturn=20
..................
--类识的插入表操作有5个--最后if @@Error<>0
rollback transaction
select @DataReturn=10
return (10)
else
commit transaction
select @DataReturn=20请高手指点
多谢!
^^这里是不是笔误??begin
select @provincecode=count(*)+1 from Provincelist
insert provincelist values(@provincecode,@Province)
end
else
begin
select @provincecode=ProvinceCode from provincelist where ProvinceName=@Province
^^^整个ELSE的处理没有必要,还是有些代码没有贴出来??end
if @@Error<>0
rollback transaction
select @DataReturn=10
return (10)
else
commit transaction
select @DataReturn=20
還有一些問題,我待會再看.
望斑竹帮忙!小弟吐血拿出源代码,求高人!!!!一定给分!!!!!!create procedure AddOneHigh
(
@StreamNumber varchar(15),
@IdentityNumber varchar(50),
@Name varchar(50),
@Sex char(2),
@BloodType varchar(4),
@Birthday varchar(20),
@BornProvince varchar(40),
@BornCity varchar(40),
@YearLimitation varchar(2),
@GraduateSchool varchar(40),
@MasterName varchar(50),
@SchoolAddress varchar(100),
@DegreeLevel varchar(20),
@StudyTime varchar(25),
@GraduateNumber varchar(50),
@GraduateCardName varchar(50),
@DeliverDate varchar(20),
@Memo varchar(200),
@DataReturn tinyint OUTPUT
)
AS
declare @StreamNumbera bigint
declare @provincecode tinyint
declare @citycode int
declare @schoolcode int
declare @studytimecode intbegin transaction--把C++builder中的String转换为bigint型
select @StreamNumbera=convert(bigint,@StreamNumber)
--调用存储过程插入分支表并获得其相应的代码值exec @provincecode=InsertProvince @BornProvince=@BornProvince
exec @citycode=InsertCity @BornCity=@BornCity
exec @schoolcode=InsertSchool @GraduateSchool=@GraduateSchool,@MasterName=@MasterName,@SchoolAddress=@SchoolAddress
exec @studytimecode=InsertStudytime @StudyTime=@StudyTimeif @provincecode=0 or @citycode=0 or @schoolcode=0 or @studytimecode=0
begin
--插入代码时出错
rollback transaction
select @DataReturn=10
return (10)
end
--插入mainindex表
insert mainindex values(@StreamNumbera,@IdentityNumber,@GraduateNumber)
if @@Error<>0
begin
raiserror('插入mainindex时,记录时出错!请检查你的--流水号--或--证书编号--有没有与以前的重复!',16,1)
rollback transaction
select @DataReturn=50
return (50)
end
--插入证书信息
insert highcardinfo(GraduateNumber,GraduateCardName,DeliverDate) values(@GraduateNumber,@GraduateCardName,@DeliverDate)
if @@Error<>0
begin
raiserror('插入记录时出错!请检查你的--证书编号--有没有重复!',16,1)
rollback transaction
select @DataReturn=30
return (30)
end--插入个人信息表
insert highpersoninfo values(@StreamNumbera,@IdentityNumber,@Name,@Sex,@BloodType,@Birthday,@provincecode,@citycode,@YearLimitation,@schoolcode,@DegreeLevel,@studytimecode,@GraduateNumber,@Memo)
if @@Error<>0
begin
raiserror('插入记录时出错!请检查你的--流水号--或--证书编号--有没有重复!',16,1)
rollback transaction
select @DataReturn=40
return (40)
end
else
begin
commit transaction
select @DataReturn=20
return (20)
end
GO
if @@error<>0 goto quiterror
.....if @@error<>0 goto quiterror--取消在中间的提交
在过程末统一提交
commit transaction
goto endsave
quiterror:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: