USE [StrataFlix]
GO

/****** Object: StoredProcedure [dbo].[PeopleMaintenance_RetrieveAllData] Script Date: 03/01/2010 14:00:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PeopleMaintenance_RetrieveAllData] @peoplePk INT,
@includePeople BIT
AS
BEGIN

-- Determine if the people result set should be included
IF @includePeople = 1
BEGIN
-- Retrieve the people data result set
SELECT
*
FROM People
WHERE pl_pk = @peoplePk;
END

-- Retrieve all of the movies (filmography) for the person
SELECT
MovieCast.*,
Movies.mv_Title,
Movies.mv_Year
FROM MovieCast
LEFT JOIN Movies ON mc_mv_pk = mv_pk
WHERE mc_pl_pk = @peoplePk
ORDER BY Movies.mv_Year;

-- Retrieve the images associated with the people
SELECT
*
FROM PeopleImages
WHERE pli_pl_pk = @peoplePk;

END
GO

EXEC sys.sp_addextendedproperty @name=N'DDT_Priority', @value=10 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'PeopleMaintenance_RetrieveAllData'
GO