/* -------------------------------------------------------------------------
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
-------------------------------------------------------------------------------
-- These are optional tables to return a skill id or badge issuer for a name.
-- These potentially simplify use of the CreatePersonInstance procedure
-- defined below.
CREATE FUNCTION GetSkillId(@Title NVARCHAR(50)) RETURNS INT AS
BEGIN
DECLARE @Result INT
SELECT TOP 1 @Result = Id FROM SKILL WHERE Title = @Title
RETURN @Result
END
GO
-------------------------------------------------------------------------------
CREATE FUNCTION GetBadgeIssuerId(@Name NVARCHAR(50)) RETURNS INT AS
BEGIN
DECLARE @Result INT
DECLARE @Pattern NVARCHAR(52)
SET @Pattern = '%' + @Name + '%';
SELECT TOP 1 @Result = Id
FROM PERSON_PROFILE
JOIN PERSON ON PERSON_PROFILE.PersonId = PERSON.Id
WHERE ProfileId = 'Scty' AND dbo.PersonName(PERSON.Id) LIKE @Pattern
RETURN @Result
END
GO
-------------------------------------------------------------------------------
-- NOTE: The assignment instructions seem to suggest that each person is
-- always assigned one skill. This is not necessarily the case. The
-- PERSON_SKILL table allows zero or more associations between people and
-- skills. Regardless, this stored procedure will always create a person
-- with an initially-assigned skill.
CREATE PROCEDURE CreatePersonInstance(
@BadgeIssuerId INT,
@FirstName NVARCHAR(20),
@LastName NVARCHAR(20),
@MiddleName NCHAR(1),
@DateOfBirth DATETIME,
@EmploymentStatus NCHAR(1),
@Money MONEY,
@SkillId INT)
AS
-- Verify the employment status is valid.
IF @EmploymentStatus NOT IN ('F', 'P')
BEGIN
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
-- Verify the skill id is valid.
DECLARE @TempSkillTable TABLE ( one INT )
INSERT INTO @TempSkillTable SELECT TOP 1 1 FROM SKILL WHERE Id = @SkillId
IF @@ROWCOUNT <> 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Illegal skill id.', 16, -1, @SkillId)
RETURN 50000
END
-- Verify the badge issuer is valid.
DECLARE @TempBadgeTable TABLE ( one INT )
INSERT INTO @TempBadgeTable SELECT TOP 1 1 FROM PERSON_PROFILE
WHERE ProfileId='Scty' AND PersonId=@BadgeIssuerId;
IF @@ROWCOUNT <> 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Illegal badge issuer id.', 16, -1, @BadgeIssuerId)
RETURN 50000
END
-- Determine the next available badge id.
DECLARE @BadgeId INT
SELECT @BadgeId = MAX(Id) FROM BADGE;
SET @BadgeId = @BadgeId + 1;
-- Create the new badge record with this id.
INSERT INTO BADGE (Id, IssuingPerson, IssueDate)
VALUES (@BadgeId, @BadgeIssuerId, GETDATE());
-- Determine the next available person id.
DECLARE @PersonId INT
SELECT @PersonId = MAX(Id) FROM PERSON;
SET @PersonId = @PersonId + 1;
-- Create the new person record with this badge id.
INSERT INTO PERSON
(
Id,
FirstName,
LastName,
MiddleName,
DateOfBirth,
EmploymentStatus,
Badge
)
VALUES
(
@PersonId,
@FirstName,
@LastName,
@MiddleName,
@DateOfBirth,
@EmploymentStatus,
@BadgeId
);
-- Create a record in the FULL_TIME or PART_TIME table.
IF @EmploymentStatus = 'F'
INSERT INTO FULL_TIME ( Id, AnnualWage )
VALUES ( @PersonId, @Money );
ELSE
INSERT INTO PART_TIME ( Id, HourlySalary )
VALUES ( @PersonId, @Money );
-- Create a record in the PERSON_SKILL table that associates the new person
-- with an initial skill. The assessment and review dates are initially
-- null.
INSERT INTO PERSON_SKILL ( SkillId, PersonId )
VALUES ( @SkillId, @PersonId );
COMMIT TRANSACTION
-- Return the person id.
SELECT Id FROM PERSON WHERE Id = @PersonId
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';
-- Display information about the person; join the full or part time tables.
-- This returns:
--
-- FirstName LastName MiddleName DateOfBirth EmpStatus Badge AnnualWage
-- - --------- -------- ---------- ----------- --------- ----- ----------
-- 1 Cheng Yu NULL 1984-10-13 F 4276 15000.00
--
SELECT FirstName, LastName, MiddleName, DateOfBirth,
EmploymentStatus, Badge, AnnualWage
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 created.
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
*/
