本文介绍了将值传递给一张表并在父表中更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两张桌子.文件名EmpID(PK) |姓名 |类型 ID(FK)
I have two tables. dbo.EmpEmpID(PK) | Name | TypeID(FK)
dbo.EmpType类型ID(PK) |输入
dbo.EmpTypeTypeID(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.EmpI 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
这篇关于将值传递给一张表并在父表中更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!