USP_WEBDASHBOARDFOLDER_GETITEMLIST

create procedure dbo.USP_WEBDASHBOARDFOLDER_GETITEMLIST(@APPUSERID uniqueidentifier,
    @PATH nvarchar(1000))
as
    set nocount on;

    declare @PARENTID uniqueidentifier;

    select @PARENTID = ID from dbo.WEBDASHBOARDFOLDER where PATH = @PATH;

    select WEBDASHBOARDFOLDER.ID, 
        ITEMTYPE = 1, 
        WEBDASHBOARDFOLDER.NAME TITLE,
        WEBDASHBOARDFOLDER.NAME, 
        WEBDASHBOARDFOLDER.DESCRIPTION, 
        coalesce(PARENTFOLDER.PATH, '') PARENTPATH,
        (select count(*) from dbo.WEBDASHBOARDFOLDER CHILDFOLDER where CHILDFOLDER.PARENTID = WEBDASHBOARDFOLDER.ID) + (select count(*) from dbo.WEBDASHBOARDPAGECATALOG where WEBDASHBOARDFOLDERID = WEBDASHBOARDFOLDER.ID) ITEMCOUNT
    from dbo.WEBDASHBOARDFOLDER 
    left join dbo.WEBDASHBOARDFOLDER PARENTFOLDER on WEBDASHBOARDFOLDER.PARENTID = PARENTFOLDER.ID
    where WEBDASHBOARDFOLDER.PARENTID = @PARENTID 
        or (@PATH = '' and WEBDASHBOARDFOLDER.PARENTID is null)
    union
    select WEBDASHBOARDPAGECATALOG.ID,
        ITEMTYPE = 2,
        WEBDASHBOARDPAGECATALOG.TITLE,
        WEBDASHBOARDPAGECATALOG.NAME,
        WEBDASHBOARDPAGECATALOG.DESCRIPTION,
        coalesce(WEBDASHBOARDFOLDER.PATH, '') PARENTPATH,
        0 ITEMCOUNT
    from dbo.WEBDASHBOARDPAGECATALOG
    left join dbo.WEBDASHBOARDFOLDER on WEBDASHBOARDFOLDER.ID = WEBDASHBOARDPAGECATALOG.WEBDASHBOARDFOLDERID
    where dbo.UFN_SECURITY_APPUSER_GRANTED_WEBDASHBOARDPAGEVIEW(@APPUSERID, WEBDASHBOARDFOLDER.PATH, WEBDASHBOARDPAGECATALOG.NAME) = 1
        and (WEBDASHBOARDPAGECATALOG.WEBDASHBOARDFOLDERID = @PARENTID 
            or (@PATH = '' and WEBDASHBOARDPAGECATALOG.WEBDASHBOARDFOLDERID is null))

    order by ITEMTYPE, TITLE;