First of all, follow this link:
Creating Stored Procedures[
^]
The below procedure selects employee by his/her
FisrtName
and
LastName
. If employee does not exists, adding data into
Employee
table, then adding data into
Employee_Detail
table.
CREATE STORED PROCEDURE AddEmpIntoDept
@LastName NVARCHAR(50),
@FirstName NVARCHAR(50),
@DepId INT
AS
BEGIN
DECLARE @empid INT
DECLARE @retval INT
SET NOCOUNT ON;
SELECT @empid = ISNULL(EmpId,0), FirstName, LastName
FROM Employees
WHERE FirstName = @FirstName AND LastName = @LastName;
IF @empid = 0
BEGIN
INSERT INTO Employess (FirstNam, LastName)
VALUES (@FirstName, @LastName)
SET @empid = @@IDENTITY
END
INSERT INTO Employees_Detail (EmpId, DeptId)
VALUES(@DepId, @empid)
SET @retVal = @@IDENTITY
RETURN @retval
END
GO