来个帮忙 看看 存储过程 事务 上面的问题

[Wicho开源开发中]

sql+=@"BEGIN TRANSACTION 
				if not exists(select * from T_PersonInfo where id=@empid) 
					INSERT INTO T_PersonInfo(id,name,k_rate,AdminRate,SubsidyRate,bank_account,bank_Name,bank_City,company_place,orgId,dept_cost_center,status,person_cost_center,job,jobPart,part_info,in_job_date,work_city,phone,email,education,sex,person_id,passport_id,birthday,nationality,politics,marriage,reg_place,reg_address,linkman,emergent_phone,live_place,live_address,zip_code,contract_timelimit,contract_date_op,contract_date_ed,probation,change_date,BenchId,stateFlag)
					select @empid,@name,@k_rate,@AdminRate,@SubsidyRate,@bank_account,@bank_Name,@bank_City,@company_place,@orgId,@dept_cost_center,@status,@person_cost_center,@job,@jobPart,@part_info,@in_job_date,@work_city,@phone,@email,@education,@sex,@person_id,@passport_id,@birthday,@nationality,@politics,@marriage,@reg_place,@reg_address,@linkman,@emergent_phone,@live_place,@live_address,@zip_code,@contract_timelimit,@contract_date_op,@contract_date_ed,@probation,@change_date,@benchId,@stateFlag 
					if @@error<>0 
					ROLLBACK TRANSACTION return	
				";
				sql+=@"	Update T_ArchiveInfo Set staffId=@empid,lastModifyDate=@lastModifyDate where staffId=@oldId 
					if @@error<>0 
					ROLLBACK TRANSACTION return
					";
				
				sql+=@"	 if not exists(select * from T_EmailGroupRelation where staffId=@empid)  ";
					for(int i=0;i<list.Count;i++){
						sql+=@"Insert Into T_EmailGroupRelation(id,staffId,emailGroup,bolSend) values(@id,@empid,@emailGroup"+i+",@bolSend"+i+");";
					}		
					sql+=@"
					if @@error<>0 
					ROLLBACK TRANSACTION return

					if not exists(select * from T_EmailGroupRelation where staffId=@oldId) delete T_PersonInfo where id=@oldId
					if @@error<>0 
					ROLLBACK TRANSACTION return
					else
					COMMIT TRANSACTION			
			";

帮忙看看 这段的代码的错误

[昵称被占用了]

sql+=@"BEGIN TRANSACTION
if not exists(select * from T_PersonInfo where id=@empid)
INSERT INTO T_PersonInfo(id,name,k_rate,AdminRate,SubsidyRate,bank_account,bank_Name,bank_City,company_place,orgId,dept_cost_center,status,person_cost_center,job,jobPart,part_info,in_job_date,work_city,phone,email,education,sex,person_id,passport_id,birthday,nationality,politics,marriage,reg_place,reg_address,linkman,emergent_phone,live_place,live_address,zip_code,contract_timelimit,contract_date_op,contract_date_ed,probation,change_date,BenchId,stateFlag)
select @empid,@name,@k_rate,@AdminRate,@SubsidyRate,@bank_account,@bank_Name,@bank_City,@company_place,@orgId,@dept_cost_center,@status,@person_cost_center,@job,@jobPart,@part_info,@in_job_date,@work_city,@phone,@email,@education,@sex,@person_id,@passport_id,@birthday,@nationality,@politics,@marriage,@reg_place,@reg_address,@linkman,@emergent_phone,@live_place,@live_address,@zip_code,@contract_timelimit,@contract_date_op,@contract_date_ed,@probation,@change_date,@benchId,@stateFlag
if @@error<>0
ROLLBACK TRANSACTION return
";

-->

sql+=@"BEGIN TRANSACTION
if not exists(select * from T_PersonInfo where id=@empid)
begin
INSERT INTO T_PersonInfo(id,name,k_rate,AdminRate,SubsidyRate,bank_account,bank_Name,bank_City,company_place,orgId,dept_cost_center,status,person_cost_center,job,jobPart,part_info,in_job_date,work_city,phone,email,education,sex,person_id,passport_id,birthday,nationality,politics,marriage,reg_place,reg_address,linkman,emergent_phone,live_place,live_address,zip_code,contract_timelimit,contract_date_op,contract_date_ed,probation,change_date,BenchId,stateFlag)
select @empid,@name,@k_rate,@AdminRate,@SubsidyRate,@bank_account,@bank_Name,@bank_City,@company_place,@orgId,@dept_cost_center,@status,@person_cost_center,@job,@jobPart,@part_info,@in_job_date,@work_city,@phone,@email,@education,@sex,@person_id,@passport_id,@birthday,@nationality,@politics,@marriage,@reg_place,@reg_address,@linkman,@emergent_phone,@live_place,@live_address,@zip_code,@contract_timelimit,@contract_date_op,@contract_date_ed,@probation,@change_date,@benchId,@stateFlag

if @@error<>0
begin
ROLLBACK TRANSACTION return
end
end
";


其他段类似修改

[--小F--]

ORACLE的也应该是
begin TRANSACTION
..
end TRANSACTION
吧？

[Wicho开源开发中]

先试试 ，先行谢过

[--小F--]

应该begin..end一一对应的吧。

[快溜]

为什么不写到存储过程里，拼接字符串很容易出错，你可以调试出来看看你的sql

[Wicho开源开发中]

因为有封装好的 底层代码,直接 操作 数据工厂

[唐诗三百首]

因为开始了事务(BEGIN TRANSACTION),

应该是哪个分支判断, 最后没有给予commit transaction或rollback transaction

[Wicho开源开发中]

忘了 结贴鸟 久等

[-晴天]

[Quote=引用 8 楼 shwicho 的回复:]

忘了 结贴鸟 久等
[/Quote]

哈哈......

[Wicho开源开发中]

第一次写 带事务的 存储过程 见谅！！！！！！！！！！