V_QUERY_SMARTQUERYUSERDEFINED

create function dbo.V_QUERY_SMARTQUERYUSERDEFINED
(
  @RECORDTYPEID uniqueidentifier = null
)
returns @SMARTQUERYUSERDEFINED table
(
  ID uniqueidentifier,
  NAME nvarchar(60),
  DESCRIPTION nvarchar(1000),
  RECORDTYPE nvarchar(50)
)

with execute as caller
as
begin

  with xmlnamespaces
  (
    'bb_appfx_smartquery' as ns,
    'bb_appfx_commontypes' as c
  )
  insert into @SMARTQUERYUSERDEFINED
  (
    ID,
    NAME,
    DESCRIPTION,
    RECORDTYPE
  )
  select
    SMARTQUERYCATALOG.ID,
    SMARTQUERYCATALOG.UINAME,
    SMARTQUERYCATALOG.DESCRIPTION,
    RECORDTYPE.NAME
  from dbo.SMARTQUERYCATALOG
    left join dbo.RECORDTYPE on SMARTQUERYCATALOG.RECORDTYPEID = RECORDTYPE.ID
  where 
    (@RECORDTYPEID is null or
    SMARTQUERYCATALOG.RECORDTYPEID = @RECORDTYPEID)
    and SMARTQUERYCATALOG.SMARTQUERYSPEC.exist('ns:SmartQuerySpec[1]/c:MetaTags[1]/AdHocQuerySaveSmartQueryRequest[1]') = 1
  order by SMARTQUERYCATALOG.UINAME;

  return;

end