Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Add Comment
Please, Sign In to add comment