HWA2_P5_3.sql

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

   PART 5.3
   Provide the script that will create a stored procedure that will select
   the Project Title, name and role of all personnel associated with all
   projects. Use the attribute names 'Project Title', 'Member',  and
   'Project Role'.  Name the stored procedure projectPersonnel.
   ------------------------------------------------------------------------- */

USE DB9975
GO

CREATE PROCEDURE ProjectPersonnel AS
	SELECT
		PROJECT.Title             AS [Project Title],
		dbo.PersonName(PERSON.Id) AS [Member],
		PROJECT_ROLE.Description  AS [Project Role]

	FROM PROJECT_ASSIGNMENT

	JOIN PERSON       ON PROJECT_ASSIGNMENT.PersonId  = PERSON.Id
	JOIN PROJECT      ON PROJECT_ASSIGNMENT.ProjectId = PROJECT.Id
	JOIN PROJECT_ROLE ON PROJECT_ASSIGNMENT.RoleId    = PROJECT_ROLE.Id

	ORDER BY [Project Title], [Member], [Project Role]
GO

EXECUTE ProjectPersonnel
GO