HWA2_P5_2.sql

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

   PART 5.2
   Create a stored procedure using the JOIN statement which will provide a
   listing of all employees names not assigned to any projects. Name the
   stored procedure UnassignedProjectPersonnel.
   ------------------------------------------------------------------------- */

USE DB9975
GO

CREATE PROCEDURE UnassignedProjectPersonnel AS
	SELECT dbo.PersonName(PERSON.Id) AS [Name]

	FROM PERSON

	WHERE PERSON.Id NOT IN
	(
		SELECT PROJECT_ASSIGNMENT.PersonId
		FROM   PROJECT_ASSIGNMENT
	)

	ORDER BY [Name]
GO

EXECUTE UnassignedProjectPersonnel
GO