用存储过程进行多个表的插入问题－－－－

Question

我用存储过程进行多个表的插入
可就是插不进去 也没有错误提示
希望大家帮忙看看 谢谢
下面是存储过程的代码
create procedure add1
 @employee_id char(10),--员工号码
 @department_id char(10),--部门编号
 @employee_name char(10),--姓名
 @department_name char(10),--部门名称
 @minchen char(10),
 @now datetimeas 
begin transactionupdate department
set department_pnum=department_pnum+1
where department_id=@department_id
if @@error<>0 goto proinsert into chidaokoufaj
values(@department_id,@department_name,@employee_id,@employee_name,0,0.0,@now)
if @@error<>0 goto proinsert into zaotui
values(@department_id,@department_name,@employee_id,@employee_name,0,0.0,@now)
if @@error<>0 goto proinsert into fulisalary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0.0,@now)
if @@error<>0 goto proinsert into gangweijjsalary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0.0,@now)
if @@error<>0 goto proinsert into gangweisalary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0.0,@now)
if @@error<>0 goto proinsert into gonglinsalary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0.0,@now)
if @@error<>0 goto proinsert into gongzijibsalary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0.0,@now)
if @@error<>0 goto pro
insert into jianbansalary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0,0.0,0.0,@now)
if @@error<>0 goto proinsert into kuanggongkfj
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0,0.0,0.0,@now)
if @@error<>0 goto proinsert into othersalary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0.0,@now)
if @@error<>0 goto pro
insert into shengccesalary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0.0,@now)
if @@error<>0 goto pro
insert into shenghuobt_salary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0.0,@now)
if @@error<>0 goto proinsert into tesjtsalary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0.0,@now)
if @@error<>0 goto pro
insert into weiwcrwkfjsalary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0.0,@now)
if @@error<>0 goto proinsert into dqcesalary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0.0,@now)
if @@error<>0 goto procommit transactionreturn 0
pro:
rollback transaction
return 1
GO

Answer 1

由于你用的是事物提交的方式，只要有一个执行失败，那么全部操作都被回滚，所以就一条都没插入。你把begin tran和commit tran 这些去掉，这样执行的话，你就可以看到他抛出的错误提示，会说明在那个表的插入时发生错误。当然，你可以到查询分析器右键这个存储过程，调试，这里可以对存储过程单步调试。

Answer 2

完全同意whbo观点. 调出异常看看.

Answer 3

建议调用pro这个回滚事务之前，显示一些操作失败原因，这样才好判断。
比如：
insert into dqcesalary
values(@department_id,@department_name,@employee_id,@employee_name,@minchen,0.0,@now)
if @@error<>0 
begin
  print 'insert into dqcesalary Failure'
  goto pro
end

Answer 4

你把begin tran和commit tran 这些去掉,单步执行，看看那里错了

Answer 5

在查询分析器中仔细调试调试，把出错时的信息print出来