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