USP_DATALIST_VIDEO

CREATE procedure dbo.USP_DATALIST_VIDEO
(
  @ID uniqueidentifier = null,
  @EVENTID uniqueidentifier = null,
  @CONSTITUENTID uniqueidentifier = null,
  @STATUS nvarchar(2) = null,
  @PRIVATE bit = 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 
        VIDEO.[ID],
        VIDEO.[CONSTITUENTID],
        VIDEO.[EVENTID],
            VIDEO.[VIDEOID],
        VIDEO.[NAME],
        VIDEO.[CAPTION],
        VIDEO.[URL],
            VIDEO.[STATUS],
        VIDEO.[FILEPATH],
            VIDEO.[PRIVATE],
            VIDEO.[TYPECODE],
            VIDEO.[TYPE]
    from 
        dbo.VIDEO (NOLOCK)
    WHERE
      ((EVENTID = @EVENTID'
    IF @ISADMIN = 0
        set @script = @script + ' AND VIDEO.CONSTITUENTID = ISNULL(@CONSTITUENTID,''00000000-0000-0000-0000-000000000000'')'

        IF @ISADMIN = 1
        set @script = @script + ' AND VIDEO.CONSTITUENTID IS NULL '    

    set @script = @script + ')'

    IF @CONSTITUENTID IS NOT NULL
    set @script = @script + ' OR (VIDEO.CONSTITUENTID = @CONSTITUENTID)'

    IF @ID IS NOT NULL
    set @script = @script + ' OR (ID= @ID)'

    set @script = @script + ')

    AND TYPECODE = @TYPECODE
        AND STATUS = ISNULL(@STATUS,STATUS)'

        IF @PRIVATE IS NOT NULL
        set @script = @script + ' AND PRIVATE = @PRIVATE'

    SET @PARAMDEF = '@ID uniqueidentifier = null,@EVENTID uniqueidentifier, @CONSTITUENTID uniqueidentifier = null, @STATUS nvarchar(2) = null,
                    @PRIVATE bit = null, @TYPECODE tinyint = 0, @ISADMIN bit = 0'

    exec sp_executesql @script, @PARAMDEF, @ID,@EVENTID,@CONSTITUENTID, @STATUS,@PRIVATE,@TYPECODE,@ISADMIN