USP_NOTIFICATION_GETRECORDNOTIFICATIONS

CREATE procedure dbo.USP_NOTIFICATION_GETRECORDNOTIFICATIONS
(
  @RECORDTYPEID uniqueidentifier,
  @RECORDID nvarchar(max),
  @APPUSERID uniqueidentifier
)
as
  set nocount on;

  select
    NOTIFICATION.ID,
    NOTIFICATION.NAME,
    NOTIFICATION.MESSAGE,
    NOTIFICATION.DISPLAYNOTIFICATIONWINDOW,
    RECORDTYPE.NAME as RECORDTYPENAME,
    @RECORDID as RECORDID
  from dbo.NOTIFICATION
  inner join dbo.RECORDTYPE on NOTIFICATION.RECORDTYPEID = RECORDTYPE.ID
  where
    NOTIFICATION.RECORDTYPEID = @RECORDTYPEID and
    (
      (NOTIFICATION.VALIDUNTIL is null) or 
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      (cast(getdate() as date) <= cast(NOTIFICATION.VALIDUNTIL as date))
    ) and
    (
      (NOTIFICATION.RECORDIDSETREGISTERID is null) or 
      (@RECORDID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS(NOTIFICATION.RECORDIDSETREGISTERID)))
    ) and
    (
      (NOTIFICATION.APPLYTOCODE = 0) or 
      (@APPUSERID  in (select ID from dbo.UFN_IDSETREADER_GETRESULTS(NOTIFICATION.APPUSERIDSETREGISTERID)))
    );

  return 0;