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

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

C# code

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
";
  

其他段类似修改

[解决办法]
ORACLE的也应该是
begin TRANSACTION
..
end TRANSACTION
吧？
[解决办法]
为什么不写到存储过程里，拼接字符串很容易出错，你可以调试出来看看你的sql
[解决办法]
因为开始了事务(BEGIN TRANSACTION),

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

查看更多 下一篇