spGet_ATHLETICS_EVENTS

    CREATE PROCEDURE [dbo].[spGet_ATHLETICS_EVENTS]
    @ID int = null,
    @StartingAfter datetime,
    @StartingBefore datetime, 
    @LocationID int = null,
    @TeamID int = null,
    @OpponentGuid uniqueidentifier = null,
    @ClientSitesID int
AS
SET NOCOUNT ON

DECLARE @EmptyGuid uniqueidentifier;
SET @EmptyGuid = '00000000-0000-0000-0000-000000000000'

DECLARE @temp table(id int);
DECLARE @hasFilter bit;
SET @hasFilter=0;

IF @ID <= 0
    Set @ID=null;

IF @LocationID <= 0
    Set @LocationID=null;

IF @TeamID <= 0
    Set @TeamID=null;

if @OpponentGuid = @EmptyGuid
    SET @OpponentGuid = null;

if @ID is not null
begin
    SET @hasFilter=1;
    insert into @temp(id)
    select id from dbo.ATHLETICS_EVENTS    where id=@id
end
else
begin
    if @LocationID is not null
    begin
        SET @hasFilter=1;
        insert into @temp(id)
        select id from dbo.ATHLETICS_EVENTS    where locationid=@LocationID
    end
    if @TeamID is not null
    begin
        SET @hasFilter=1;
        insert into @temp(id)
        select id from dbo.ATHLETICS_EVENTS    where teamid=@TeamID
    end
    if @OpponentGuid is not null
    begin
        SET @hasFilter=1;
        insert into @temp(id)
        select id from dbo.ATHLETICS_EVENTS    where OpponentID=@OpponentGuid
    end
end

if @hasFilter=0
begin
    insert into @temp(id) select id from athletics_events
end

SELECT ae.[ID], 
    ae.[TeamID], 
    ce.Category CategoryGuid,
    cte1.Description [Category], 
    ae.[Stats], 
    ae.[Recap], 
    ae.[TransportInfo], 
    ae.[LocationID],
    al.[Name] Location,
    ae.OpponentID [OpponentGuid],
    cte2.Description [Opponent],
    ae.[ContactID],
    ae.Score,
    ae.ScoreOutcome,
    ce.ID CalendarEventID,
    ce.EventCalendarID,
    ce.BeginDate,
    ce.EndDate,
    ce.Title,
    ce.Details,
    ce.TimeZoneID
FROM ATHLETICS_EVENTS ae
INNER JOIN (select id from @temp group by id) t ON ae.id=t.id
INNER JOIN dbo.CalendarEvent ce ON ce.AthleticEventID = ae.ID
LEFT JOIN dbo.SiteCodeTableEntries cte1 ON
CASE ce.category WHEN '' THEN NULL ELSE category END = cte1.EntryGuid
LEFT JOIN dbo.ATHLETICS_LOCATIONS al ON
ae.LocationID = al.ID
LEFT JOIN dbo.SiteCodeTableEntries cte2 ON
ae.OpponentID = cte2.EntryGuid
LEFT JOIN dbo.athletics_teams at ON
at.ID = ae.TeamID
where at.ClientSitesID = @ClientSitesID
and ce.BeginDate > @StartingAfter
and ce.EndDate < @StartingBefore