HWA2_P2_4.sql

/* -------------------------------------------------------------------------
   Yu Cheng
   ICS 321 Assignment 2
   October 23, 2008

   PART 2.4
   Write a stored procedure that will list all personnel with their name as
   'Employee', badge number, the name of the person who assign the badge as
   'Issued By', and the date of the assignment as 'Assigned Date'. Name the
   stored procedure [PersonnelSecurityBadgeAssignment]
   ------------------------------------------------------------------------- */

USE DB9975
GO

CREATE PROCEDURE PersonnelSecurityBadgeAssignment AS
	SELECT
		dbo.PersonName(PERSON.Id)           AS Employee,
		Badge                               AS BadgeNumber,
		dbo.PersonName(BADGE.IssuingPerson) AS [Issued By],
		IssueDate                           AS [Assigned Date]

	FROM PERSON

	JOIN BADGE ON PERSON.Badge = BADGE.Id
GO

EXECUTE PersonnelSecurityBadgeAssignment
GO