将值传递给一张表并在父表中更新 [英] Passing value to one table and updating in parent table

问题描述

我有两张桌子.文件名EmpID(PK) |姓名 |类型 ID(FK)

I have two tables. dbo.Emp EmpID(PK) | Name | TypeID(FK)

dbo.EmpType类型ID(PK) |输入

dbo.EmpType TypeID(PK) | Type

TypeID:仅在 dbo.EmpType 中自动递增

TypeID: AutoIncrement only in dbo.EmpType

当我更新 Emptype 中 TypeID 的值时，它需要存储在 dbo.Emp 的 TypeID 中我使用了不起作用的SP.

When I update value for TypeID in Emptype, it needs get stored in TypeID of dbo.Emp I used SP which is not working.

Create Procedure bridge_Type(
@EmpID INT,
@Name   varchar(50),
@Mob2   numeric(10,0),
@Email  varchar(50),
@Type   varchar(50)
)
AS 
DECLARE @TYPEID int
Set NOCOUNT OFF

BEGIN TRANSACTION
Insert into dbo.Emp VALUES (@EmpID, @Name, @Mob2, @Email, @TYPEID)
IF @@ERROR <> 0
BEGIN 
ROLLBACK
RETURN
END

INSERT INTO dbo.EmpType VALUES (@Type)
IF @@ERROR <> 0 
BEGIN     
ROLLBACK     
RETURN 
END  

declare @id int
SET @id= @TYPEID;
Update Dbo.Emp 
Set @TYPEID= (Select TypeID from dbo.EmpType
          WHERE  TypeID=@typeID)
COMMIT 

推荐答案

Try This SP, This will first insert the EmpType, and From SCOPE_IDENTITY() 它会得到插入的标识值，然后插入 emp.

Try This SP, This will first insert the EmpType, and From SCOPE_IDENTITY() it will get the Inserted Identity Value, and then insert emp.

Create Procedure bridge_Type(
@EmpID INT,
@Name   varchar(50),
@Mob2   numeric(10,0),
@Email  varchar(50),
@Type   varchar(50)
)
AS 
DECLARE @TYPEID int
Set NOCOUNT OFF

BEGIN TRANSACTION

INSERT INTO dbo.EmpType VALUES (@Type)

SET @TYPEID = SCOPE_IDENTITY()

IF @@ERROR <> 0 
BEGIN     
ROLLBACK     
RETURN 
END 


Insert into dbo.Emp VALUES (@EmpID, @Name, @Mob2, @Email, @TYPEID)
IF @@ERROR <> 0
BEGIN 
ROLLBACK
RETURN
END

COMMIT 

这篇关于将值传递给一张表并在父表中更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！