spSitePageTab

CREATE  procedure [dbo].[spSitePageTab] (
    @ClientSitesID     int, 
    @pageId            integer = 0,
    @tabId            integer = 0)
as
begin

if @tabid <= 0
    BEGIN
        --CR244750-052406
        SELECT [PageName] FROM SitePages WHERE [ID] = @PageID
    END
ELSE
    BEGIN
        select [Name], [TabID], PageName, [Tab]
        from 
        (    select distinct sp.PageName + COALESCE(' - ' + ctt.text,'') as [Name], 
            convert(varchar(20),sp.id)+';'+COALESCE(convert(varchar(20),ctt.tab),'0') as [TabID],
            sp.PageName,COALESCE(ctt.tab, 0) as [Tab],
            case @pageId when 0 then 0 else sp.id end as MyPageId,
            case @tabId when 0 then 0 else ctt.tab end as MyTabId
            from SiteContent sc
            join PageContent pg on sc.id = pg.SiteContentID
            left join ContentTabs ctt on sc.contentTypesID = ctt.ContentTypesID 
            right join SitePages sp on sp.id=pg.SitePagesID
            where sp.ClientSitesID = @ClientSitesID and sp.IsTemplatePage = 0 
            and sp.deleted = 0 and sc.deleted = 0
        ) v
        where v.MyPageId = @pageId and v.MyTabId = @tabId
        order by [Name],[Tab]
    END


end