UFN_QUERY_PAGES

CREATE function dbo.UFN_QUERY_PAGES
(
  @RECORDTYPEID uniqueidentifier = null
)
returns @PAGES table
(
  ID uniqueidentifier,
  IMAGEKEY nvarchar(22),
  NAME nvarchar(60),
  DESCRIPTION nvarchar(1000),
  RECORDTYPE nvarchar(50),
  ISCUSTOMIZABLE bit
)
with execute as caller
as
begin

  with xmlnamespaces
  (
    'bb_appfx_pagedefinition' as bbpage
  )
  insert into @PAGES
  (
    ID,
    IMAGEKEY,
    NAME,
    DESCRIPTION,
    RECORDTYPE,
    ISCUSTOMIZABLE
  )
  select
    PAGE.ID,
    'RES:pagedefinitionspec',
    PAGE.UINAME,
    PAGE.DESCRIPTION,
    RECORDTYPE.NAME,
    coalesce(PAGE.PAGEDEFINITIONSPEC.value('/bbpage:PageDefinitionSpec[1]/@IsCustomizable', 'bit'), 1)
  from dbo.PAGEDEFINITIONCATALOG as PAGE
    left join dbo.RECORDTYPE on PAGE.CONTEXTRECORDTYPEID = RECORDTYPE.ID
  where
    @RECORDTYPEID is null or
    PAGE.CONTEXTRECORDTYPEID = @RECORDTYPEID
  order by PAGE.UINAME;

  return;

end