存储过程:
CREATE Procedure dbo.spwinMoveEmpBetweenDept
@FromDeptName nchar(10),
@ToDeptName nchar(10)
AS
Update tblEmployee
Set DeptID = dbo.udfwinGetDeptIDbyDeptName(@ToDeptName)
where DeptID = dbo.udfwinGetDeptIDbyDeptName(@FromDeptName)
GO
自定义函数:
Create Function dbo.udfwinGetDeptIDbyDeptName
(@DeptName nchar(10))
RETURNS int
AS
BEGIN
Declare @DeptID int
select @DeptID=DeptID
from tblDepartment
where DeptName=@DeptName
return @DeptID
END
CREATE Procedure dbo.spwinMoveEmpBetweenDept
@FromDeptName nchar(10),
@ToDeptName nchar(10)
AS
Update tblEmployee
Set DeptID = dbo.udfwinGetDeptIDbyDeptName(@ToDeptName)
where DeptID = dbo.udfwinGetDeptIDbyDeptName(@FromDeptName)
GO
自定义函数:
Create Function dbo.udfwinGetDeptIDbyDeptName
(@DeptName nchar(10))
RETURNS int
AS
BEGIN
Declare @DeptID int
select @DeptID=DeptID
from tblDepartment
where DeptName=@DeptName
return @DeptID
END