数据库新增向父表插入单条数据向子表插入多条数据

 

 

--增（向员工表中增加一条数据，得到一个ID，同时向部门和员工信息表中增加一条或多条数据）
--创建一个插入员工和员工和部门对应关系表的存储过程

create proc myInsertEmployeeAndMapping
(
    @depids varchar(200),
 @id int,
 @outData int out
)
as

begin

begin tran myTran--声明一个事务

insert into--向员工表中插入数据
TEmployee(
    FName,FAge,FBirthday,FJiGuan,FSexID,IsAbled,FNumber
      ,IsConfirmed      )
  values('test',50,'1988-05-15','辽宁-大连',1,1,'cw100',1 )
 
 select @id=@@IDENTITY --把插入数据的获得员工ID赋值给ID变量
 
delete from Mapping_Employee_Department where employeeID=@id --根据插入数据的员工ID删除掉员工和部门关系表中的数据
 
insert into Mapping_Employee_Department (employeeID,departmentID)  

--select @id,value from SplitToTable('1,2,3,4,5,6,7,8',',')
select @id,value from SplitToTable(@depids,',')

select @outData=@@ROWCOUNT--把获得的数据条数赋值存储过程要返回的参数

 if(@@ERROR<>0)
  rollback tran mytran--回滚事务
 else
  commit tran mytran--提交事务

end 







--在数据库中进行字符串分割成表的函数
create FUNCTION [dbo].[SplitToTable]
 (
     @SplitString nvarchar(max),
     @Separator nvarchar(10)=' '
 )
 RETURNS @SplitStringsTable TABLE
 (
 [id] int identity(1,1),
 [value] nvarchar(max)
 )
 AS
 BEGIN
     DECLARE @CurrentIndex int;
     DECLARE @NextIndex int;
     DECLARE @ReturnText nvarchar(max);
     SELECT @CurrentIndex=1;
     WHILE(@CurrentIndex<=len(@SplitString))
         BEGIN
             SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
             IF(@NextIndex=0 OR @NextIndex IS NULL)
                 SELECT @NextIndex=len(@SplitString)+1;
                 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
                 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
                 SELECT @CurrentIndex=@NextIndex+1;
             END
     RETURN;
 END