cs_postcategories_get_withoutposts.prc

community server 源码

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE dbo.cs_PostCategories_Get_WithoutPosts
	@SectionQuerySQL ntext,
	@SettingsID int
AS

SET Transaction Isolation Level Read UNCOMMITTED

CREATE TABLE #Sections
(
	SectionID int
)

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

SELECT
		C.CategoryID, C.SectionID, C.Name, C.IsEnabled, C.ParentID, C.Path, C.Description, C.SettingsID,
		C.TotalThreads, C.MostRecentPostDate, C.TotalSubThreads, C.MostRecentSubPostDate, C.DateCreated, C.FeaturedPostID
FROM cs_Post_Categories C
WHERE C.CategoryID not in (
		select CategoryID
		from cs_Posts_InCategories PiC
		inner join cs_Posts P on P.PostID = PiC.PostID
		where P.SectionID in (select SectionID from #Sections)
			and P.SettingsID = @SettingsID
		)
	and C.SectionID in (select SectionID from #Sections)
	and C.SettingsID = @SettingsID
	and C.CategoryID not in (
		select UpLevelID
		from cs_Post_Categories_Parents
		where SectionID in (select SectionID from #Sections)
			and UpLevelID <> CategoryID
		)	

DROP TABLE #Sections
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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