cs_tags_get.prc

来自「community server 源码」· PRC 代码 · 共 96 行

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Tags_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Tags_Get]
GO

CREATE PROCEDURE [dbo].[cs_Tags_Get] 
	@SectionQuerySQL ntext,
	@SettingsID int,
	@CategoryList nvarchar(4000) = null
AS

SET Transaction Isolation Level Read UNCOMMITTED

CREATE TABLE #Sections
(
	SectionID int
)

INSERT INTO #Sections (SectionID)
EXEC (@SectionQuerySQL)

if @CategoryList is null begin

	select [Name], count(*) as TotalCount
	from cs_Post_Categories C
	inner join cs_Posts_InCategories PiC on C.CategoryID = PiC.CategoryID
	where C.SettingsID = @SettingsID
		and C.SectionID in (select SectionID from #Sections)
		and C.IsEnabled = 1
	group by [Name]
	order by [Name]

end else begin

	declare @idoc int
	EXEC sp_xml_preparedocument @idoc OUTPUT, @CategoryList

	CREATE TABLE #Tags
	(
		[Name] nvarchar(255) collate database_default
	)

	insert into #Tags ([Name])
	select CONVERT(nvarchar(255), [text])
	from OPENXML(@idoc, '/Categories/Category/', 2)
	where [text] is not null

	declare @categoryCount int
	set @categoryCount = (select count(*) from #Tags)

	select [Name], count(*) as TotalCount
	from cs_Post_Categories C
	inner join cs_Posts_InCategories PiC on C.CategoryID = PiC.CategoryID
	where C.SettingsID = @SettingsID
		and C.SectionID in (select SectionID from #Sections)
		and C.IsEnabled = 1
		and PiC.PostID in (
			select P2.PostID
			from #Tags X
			inner join cs_Post_Categories C2 on X.[Name] = C2.Name
			inner join cs_Posts_InCategories PiC2 on PiC2.CategoryID = C2.CategoryID
			inner join cs_Posts P2 on PiC2.PostID = P2.PostID
			where C2.SettingsID = @SettingsID
				and C2.SectionID in (select SectionID from #Sections)
				and C2.IsEnabled = 1
				and P2.SettingsID = @SettingsID
				and P2.IsApproved = 1
			group by P2.PostID
			having count(*) = @categoryCount
			)
	group by [Name]
	having [Name] not in (select [Name] from #Tags)
	order by [Name]

	DROP TABLE #Tags

	EXEC sp_xml_removedocument @idoc

end

DROP TABLE #Sections
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_Tags_Get]  TO [public]
GO