/* -------------------------------------------------------------------------
Jade Cheng
ICS 321 Assignment 3
December 12, 2008
PART 2.2
Create a stored procedure that will create an instance of a person. This
stored procedure must also be able to create an instance of a
PERSON_SKILL, BADGE assignment, Full Time data or Part Time data
depending on the employee status. Name this procedure
CreatePersonInstance. Generate your own user defined error message for
error conditions.
------------------------------------------------------------------------- */
USE DB9975
GO
-------------------------------------------------------------------------------
-- NOTE: It is possible that more than one skill is associated with a person.
-- Therefore, this procedure and the next are implemented to add and remove
-- skills associated with people.
CREATE PROCEDURE AssociatePersonSkill( @PersonId INT, @SkillId INT ) AS
-- Lock the database in here. Use the most restrictive lock because speed
-- is not an issue.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
-- Check to make sure the person exists.
DECLARE @TempPersonTable TABLE ( one INT )
INSERT INTO @TempPersonTable
SELECT TOP 1 1
FROM PERSON
WHERE Id = @PersonId;
IF @@ROWCOUNT <> 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Invalid person id.', 16, -1, @PersonId)
RETURN 50000
END
-- Check to make sure the skill exists.
DECLARE @TempSkillTable TABLE ( one INT )
INSERT INTO @TempSkillTable
SELECT TOP 1 1
FROM SKILL
WHERE Id = @SkillId;
IF @@ROWCOUNT <> 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Invalid skill id.', 16, -1, @PersonId)
RETURN 50000
END
-- Check to make sure the skill isn't already associated.
DECLARE @TempPersonSkillTable TABLE ( one INT )
INSERT INTO @TempPersonSkillTable
SELECT TOP 1 1
FROM PERSON_SKILL
WHERE SkillId = @SkillId AND PersonId = @PersonId;
IF @@ROWCOUNT <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('The skill is already associated.', 16, -1, @SkillId)
RETURN 50000
END
-- Finally, make the association.
INSERT INTO PERSON_SKILL ( SkillId, PersonId )
VALUES ( @SkillId, @PersonId );
COMMIT TRANSACTION
GO
-------------------------------------------------------------------------------
CREATE PROCEDURE DisassociatePersonSkill( @PersonId INT, @SkillId INT ) AS
-- Lock the database in here. Use the most restrictive lock because speed
-- is not an issue.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
-- Check to make sure the skill is associated.
SELECT TOP 1 1 FROM PERSON_SKILL
WHERE SkillId = @SkillId AND PersonId = @PersonId;
IF @@ROWCOUNT <> 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('The skill is not associated.', 16, -1, @SkillId)
RETURN 50000
END
-- Finally, disassocate the person and skill.
DELETE FROM PERSON_SKILL
WHERE SkillId = @SkillId AND PersonId = @PersonId;
COMMIT TRANSACTION
GO
-------------------------------------------------------------------------------
-- NOTE: This procedure does not allow a change to the badge issuer since this
-- is something that happens only once when the employee is hired. Similarly,
-- this procedure does not allow a change to the badge number. If the person's
-- card is damaged, it is assumed that the new badge would have the same
-- number.
CREATE PROCEDURE UpdatePersonInstance(
@PersonId INT,
@FirstName NVARCHAR(20),
@LastName NVARCHAR(20),
@MiddleName NCHAR(1),
@DateOfBirth DATETIME,
@EmploymentStatus NCHAR(1),
@Money MONEY)
AS
-- Verify the employment status is valid.
IF @EmploymentStatus NOT IN ('F', 'P')
BEGIN
ROLLBACK TRANSACTION
RAISERROR('EmploymentStatus is invalid (F/P).', 16, -1, @EmploymentStatus)
RETURN 50000
END
-- Lock the database in here. Use the most restrictive lock because speed
-- is not an issue.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
-- Check to make sure the person exists.
SELECT TOP 1 1 FROM PERSON WHERE Id = @PersonId;
IF @@ROWCOUNT <> 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Invalid person id.', 16, -1, @PersonId)
RETURN 50000
END
-- Get the previous employment status.
DECLARE @OldStatus NCHAR(1)
SELECT @OldStatus = EmploymentStatus FROM PERSON WHERE Id = @PersonId;
-- Check if the employment status is changing to part time.
IF @OldStatus = 'F' AND @EmploymentStatus = 'P'
BEGIN
-- Delete the old entry.
DELETE FROM FULL_TIME WHERE Id = @PersonId;
-- Add the new record into the other table.
INSERT INTO PART_TIME ( Id, HourlySalary )
VALUES ( @PersonId, @Money );
END
-- Check if the employment status is changing to full time.
ELSE IF @OldStatus = 'P' AND @EmploymentStatus = 'F'
BEGIN
-- Delete the old entry.
DELETE FROM PART_TIME WHERE Id = @PersonId;
-- Add the new record into the other table.
INSERT INTO FULL_TIME ( Id, AnnualWage )
VALUES ( @PersonId, @Money );
END
-- Otherwise, update the full time table if they are full time.
ELSE IF @EmploymentStatus = 'F'
BEGIN
UPDATE FULL_TIME SET AnnualWage = @Money WHERE Id = @PersonId
END
-- Otherwise, update the part time table if they are part time.
ELSE IF @EmploymentStatus = 'P'
BEGIN
UPDATE PART_TIME SET HourlySalary = @Money WHERE Id = @PersonId
END
-- Update the person table.
UPDATE PERSON SET
FirstName = @FirstName,
LastName = @LastName,
MiddleName = @MiddleName,
DateOfBirth = @DateOfBirth,
EmploymentStatus = @EmploymentStatus
WHERE Id = @PersonId;
COMMIT TRANSACTION
GO
-------------------------------------------------------------------------------
-- The following commented section was used to test the procedures above.
/*
-- Find a skill called 'Bilingual'.
DECLARE @SkillId INT
SET @SkillId = dbo.GetSkillId('Bilingual');
-- Find a badge issuer with the name 'Chen'.
DECLARE @BadgeIssuerId INT
SET @BadgeIssuerId = dbo.GetBadgeIssuerId('Chen');
-- Create a new person.
EXECUTE CreatePersonInstance
@BadgeIssuerId,
'Cheng', 'Yu', NULL,
'10/13/1984',
'F',
15000,
@SkillId;
-- Check that the id exists for the person just created.
DECLARE @PersonId INT
SELECT @PersonId = Id
FROM PERSON
WHERE FirstName = 'Cheng' AND LastName = 'Yu';
-- Change some information about the person.
EXECUTE UpdatePersonInstance
@PersonId,
'Cheng2', 'Yu2', NULL,
'10/14/1984',
'P',
250
-- Display information about the person; join the full or part time tables.
-- This returns:
--
-- FirstName LastName MiddleName DateOfBirth EmpStatus Badge HourlySalary
-- - --------- -------- ---------- ----------- --------- ----- ------------
-- 1 Cheng2 Yu2 NULL 1984-10-14 P 4276 250.00
--
SELECT FirstName, LastName, MiddleName, DateOfBirth,
EmploymentStatus, Badge, HourlySalary
FROM PERSON
LEFT JOIN FULL_TIME ON PERSON.Id = FULL_TIME.Id
LEFT JOIN PART_TIME ON PERSON.Id = PART_TIME.Id
WHERE PERSON.Id = @PersonId;
-- Determine the badge id for the person just added.
DECLARE @BadgeId INT
SELECT @BadgeId = Badge FROM PERSON WHERE Id = @PersonId;
-- Display the name of the person that issued the badge.
-- This returns:
--
-- (No column name)
-- - ----------------
-- 1 Chen, Rue P
--
SELECT dbo.PersonName(IssuingPerson)
FROM BADGE
WHERE Id = @BadgeId;
-- Display the skill names associated with this person.
-- This returns:
--
-- Title (No column name)
-- ---- ----------- ----------------
-- 1 Bilingual Yu, Cheng
--
SELECT Title, dbo.PersonName(PersonId)
FROM SKILL JOIN PERSON_SKILL
ON SKILL.Id = PERSON_SKILL.SkillId
WHERE PERSON_SKILL.PersonId = @PersonId;
-- Remove the person records.
EXECUTE DeletePersonInstance @PersonId
GO
*/
