USP_DATALIST_STORY

CREATE procedure dbo.USP_DATALIST_STORY
(
  @EVENTID uniqueidentifier,
  @REGISTRANTID uniqueidentifier = null,
  @TEAMFUNDRAISINGTEAMID uniqueidentifier = null,
  @TYPECODE tinyint = 0,
  @ISADMIN bit = 0
) with execute as owner
as
    set nocount on;

    declare @script as nvarchar(4000)
    declare @PARAMDEF as nvarchar(2000)

    set @script = 
    'select 
      S.[ID],
      S.[REGISTRANTID],
      S.[EVENTID],
      S.[STORYTEXT],
      S.[FAFIMAGESID],
          S.[IMAGECODE],
          S.[IMAGE],
          V.[URL],
          S.[TEAMFUNDRAISINGTEAMID],
          S.[TYPECODE],
          S.[TYPE],
          SE.[SITEIMAGESID],
      SI.[IMAGECONTENT]
    from 
        dbo.STORY S
    LEFT OUTER JOIN dbo.VIDEO V
        ON S.FAFIMAGESID = V.ID
  LEFT OUTER JOIN dbo.SITEIMAGESEXTENSION SE
    ON S.FAFIMAGESID = SE.ID
    LEFT OUTER JOIN dbo.SITEIMAGES SI
    ON SE.SITEIMAGESID = SI.ID
  WHERE 
        S.eventid = @eventid
        AND S.TYPECODE = @TYPECODE'

    IF @TYPECODE = 0 AND @ISADMIN = 0
    set @script = @script + ' AND S.REGISTRANTID = ISNULL(@REGISTRANTID,''00000000-0000-0000-0000-000000000000'') '

    IF @TYPECODE IN (1,2,3) AND @ISADMIN = 0
    set @script = @script + ' AND S.TEAMFUNDRAISINGTEAMID = ISNULL(@TEAMFUNDRAISINGTEAMID,''00000000-0000-0000-0000-000000000000'') '

    IF @ISADMIN = 1
    set @script = @script + ' AND S.TEAMFUNDRAISINGTEAMID IS NULL AND S.REGISTRANTID IS NULL '    

    SET @PARAMDEF = '@EVENTID uniqueidentifier, @REGISTRANTID uniqueidentifier = null, @TEAMFUNDRAISINGTEAMID uniqueidentifier = null, @TYPECODE tinyint = 0, @ISADMIN bit = 0'

    exec sp_executesql @script, @PARAMDEF, @EVENTID,@REGISTRANTID,@TEAMFUNDRAISINGTEAMID,@TYPECODE,@ISADMIN