T-SQL Filter Selection

gistfile1.sql

declare @DomainCategoryID nvarchar(10)
declare @ProjectType nvarchar(50)
declare @ProjectCuratorID int
declare @Results table (
    ID int,
    ProjectType nvarchar(50),
    ProjectCuratorID int,
    Phase nvarchar(50),
    Publication nvarchar(50)
)

set @DomainCategoryID = N'0154'
set @ProjectType = N'Project'
--set @ProjectCuratorID = 14

if @DomainCategoryID is not null
begin
    with CatPaths (ID, [Path]) as (
        select 
            c.ID, 
            convert(nvarchar(1000), N'/ ' + c.ID + N' / ')
        from DomainCategory c
        left join DomainCategoryParent p on p.CategoryID = c.ID
        where p.ParentCategoryID is null
        union all
        select 
            c.ID, 
            convert(nvarchar(1000), n.[Path] + c.ID + N' / ')
        from DomainCategory c
        inner join DomainCategoryParent p on p.CategoryID = c.ID
        inner join CatPaths n on n.ID = p.ParentCategoryID
    )
    
    insert into @Results (
        ID, 
        ProjectType, 
        ProjectCuratorID, 
        Phase,
        Publication)
        select distinct
            p.ID,
            p.ProjectType,
            p.ProjectCuratorID,
            p.Phase,
            p.Publication
        from CatPaths cp
        inner join ProjectDomain pd on pd.DomainCategoryID = cp.ID
        inner join ProjectDescription p on p.ID = pd.ProjectID
        where cp.[Path] like N'%/ ' + @DomainCategoryID + N' /%'
end

else
begin
    insert into @Results (
        ID, 
        ProjectType, 
        ProjectCuratorID, 
        Phase,
        Publication)
        select
            ID, 
            ProjectType, 
            ProjectCuratorID, 
            Phase,
            Publication
        from ProjectDescription
end


if @ProjectType is not null
begin
    delete from @Results 
    where ProjectType <> @ProjectType
end

if @ProjectCuratorID is not null
begin
    delete from @Results
    where ProjectCuratorID <> @ProjectCuratorID
end

else
begin
    delete from @Results
    where Publication = N'Draft'
end;

with ProjectWithOneSection as (
    select
        r.ID,
        p.Name,
        Preview = (
            select top 1 cs.Html
            from ProjectDescriptionSection ps
            inner join ContentSection cs
                on cs.ID = ps.ID
            where ps.ProjectID = r.ID
            order by ps.[Order] desc
        )
    from @Results r
    inner join ProjectDescription p on p.ID = r.ID
)

select * from ProjectWithOneSection
where Preview is not null