fnGetMenuItems

CREATE function [dbo].[fnGetMenuItems]
( 
    @UserID int, 
    @ClientsID int, 
    @ContentID int, 
    @IsAdmin bit 
)
returns @MenuItems table
( 
    [Id] int,  
    [ParentId] int,  
    ContentID int, 
    Caption nvarchar(256), 
    Tooltip nvarchar(256), 
    SitePageID int, 
    PageTabID int, 
    URL nvarchar(2048), 
    Target nvarchar(256), 
    RightImgPad int, 
    TreeIndex nvarchar(256), 
    LinkType tinyint,
    PageGuid uniqueidentifier, 
    LoginContentCount int,
    IsActive bit,
    PageTitle nvarchar(256),
    CssClass nvarchar(100)
)
as
begin

    declare @temp table
    ( 
        [Id] int,  
        [ParentId] int,  
        ContentID int, 
        Caption nvarchar(256), 
        Tooltip nvarchar(256), 
        SitePageID int, 
        PageTabID int, 
        URL nvarchar(2048), 
        Target nvarchar(256), 
        RightImgPad int, 
        TreeIndex nvarchar(256),
        LinkType tinyint,
        PageGuid uniqueidentifier, 
        LoginContentCount int,
        IsActive bit,
        PageTitle nvarchar(256)    unique([SitePageID],[ID],[ContentID]),
        CssClass nvarchar(100)
    ) 

    insert into @temp 
    select   
        dbo.MenuItems.[Id], 
        dbo.MenuItems.ParentID, 
        dbo.MenuItems.ContentID, 
        dbo.MenuItems.Caption, 
        dbo.MenuItems.Tooltip, 
        dbo.MenuItems.SitePageID, 
        dbo.MenuItems.pageTabId, 
        dbo.MenuItems.URL, 
        dbo.MenuItems.Target, 
        dbo.MenuItems.RightImgPad, 
        dbo.MenuItems.Treeindex,
        dbo.MenuItems.LinkType, 
        NULL, 
        0 as LoginContentCount,
        dbo.MenuItems.IsActive,
        dbo.MenuItems.PageTitle,
        dbo.MenuItems.CssClass
    from dbo.MenuItems  
    where contentid=@contentID 

    update @temp 
    set [PageGuid] = sp.GUID
    from @temp t
    INNER JOIN dbo.SitePages sp ON sp.ID = t.SitePageID

    update @temp set LoginContentCount=1
    from @temp t 
    inner join (select * from [vwPageContent] vw where vw.contenttypesid=33) PC on PC.PageID = t.SitePageID    

    insert into @MenuItems ([Id], [ParentId], [ContentID], [Caption], [Tooltip], [SitePageID],[PageTabID], [URL], [Target], [RightImgPad], [TreeIndex], [LinkType], [PageGuid], [LoginContentCount], [IsActive], [PageTitle], [CssClass]) 
    select [Id], [ParentId], [ContentID], [Caption], [Tooltip], [SitePageID],[PageTabID], [URL], [Target], [RightImgPad], [TreeIndex], [LinkType], [PageGuid], [LoginContentCount], [IsActive], [PageTitle], [CssClass] 
    from @temp
    order by TreeIndex 

    return
end