cs_section_createupdatedelete.prc

解压即可使用

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- sp_helptext cs_Section_CreateUpdateDelete


create PROCEDURE [dbo].cs_Section_CreateUpdateDelete
(
	@SectionID	int out,
	@DeleteForum	bit = 0,
	@Name		nvarchar(256) = '',
	@Description	nvarchar(3000) = '',
	@ParentID	int = 0,
	@SettingsID	int,
	@GroupID	int = 0,
	@IsModerated	bit = 1,
	@IsSearchable   bit = 1,
	@DisplayPostsOlderThan	int = 0,
	@IsActive 	bit = 0,
	@EnablePostStatistics bit = 1,
	@EnableAutoDelete bit = 0,
	@EnableAnonymousPosting bit = 0,
	@AutoDeleteThreshold int = 90,
	@SortOrder int = 0,
	@PropertyNames ntext = null,
	@PropertyValues ntext = null,
	@ApplicationKey nvarchar(256) = null,
	@ApplicationType smallint = 0,
	@UserID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- Are we deleting the forum?
IF @DeleteForum = 1
BEGIN

	Select @ApplicationKey = 'Deleted Section ' + ApplicationKey FROM cs_Sections where SectionID = @SectionID and SettingsID = @SettingsID

	exec cs_system_ModerationAction_AuditEntry 19, @UserID, null, @UserID, @SectionID, @SettingsID,  @ApplicationKey


           DECLARE @SectionToDeleteParentID int
           SET @SectionToDeleteParentID = (SELECT ParentID FROM cs_Sections WHERE SectionID = @SectionID)

	-- delete the specified forum and all of its posts
	-- first we must remove all the thread tracking rows
	DELETE 
		cs_TrackedThreads
	WHERE 
		ThreadID IN (SELECT DISTINCT ThreadID FROM cs_Threads WHERE SectionID = @SectionID and SettingsID = @SettingsID)

	-- we must remove all of the moderators for this forum
	DELETE 
		cs_Moderators
	WHERE 
		SectionID = @SectionID and SettingsID = @SettingsID

	-- now we must remove all of the posts
	DELETE 
		cs_Posts
	WHERE 
		SectionID = @SectionID and SettingsID = @SettingsID

	DELETE cs_weblog_Weblogs Where SectionID = @SectionID and SettingsID = @SettingsID

	-- remove all the explicit forum permissions
	DELETE
		cs_SectionPermissions
	WHERE
		SectionID = @SectionID and SettingsID = @SettingsID

	-- finally we can delete the actual forum
	DELETE 
		cs_Sections
	WHERE 
		SectionID = @SectionID and SettingsID = @SettingsID

	DELETE 
		cs_Posts_InCategories
	WHERE
		CategoryID in (Select CategoryID FROM cs_Post_Categories where SectionID = @SectionID and SettingsID = @SettingsID)

	DELETE 
		cs_Post_Categories
	WHERE
		SectionID = @SectionID and SettingsID = @SettingsID		

	-- Clean up an child sectoins
       UPDATE
		cs_Sections
	SET 
		ParentID = @SectionToDeleteParentID
        WHERE
                ParentID = @SectionID


	RETURN
END

-- Are we updating a forum
IF @SectionID > 0
BEGIN
	-- if we are making the forum non-moderated, remove all forum moderators for this forum
	IF @IsModerated = 0
		DELETE 
			cs_Moderators
		WHERE 
			SectionID = @SectionID and SettingsID = @SettingsID

	-- Update the forum information
	UPDATE 
		cs_Sections 
	SET
		Name = @Name,
		Description = @Description,
		ParentID = @ParentID,
		SettingsID = @SettingsID,
		GroupID = @GroupID,
		IsModerated = @IsModerated,
		IsActive = @IsActive,
		IsSearchable = @IsSearchable,
		DaysToView = @DisplayPostsOlderThan,
		EnablePostStatistics = @EnablePostStatistics,
		EnableAutoDelete = @EnableAutoDelete,
		EnableAnonymousPosting = @EnableAnonymousPosting,
		AutoDeleteThreshold = @AutoDeleteThreshold,
		SortOrder = @SortOrder,
		ApplicationKey = @ApplicationKey,
		ApplicationType = @ApplicationType,
		PropertyNames = @PropertyNames,
		PropertyValues = @PropertyValues
	WHERE 
		SectionID = @SectionID and SettingsID = @SettingsID
END
ELSE
BEGIN

	if( @SortOrder = 0 ) 
	begin
		select @SortOrder = coalesce(max(SortOrder) + 1, 1) from cs_Sections where GroupID = @GroupID

		if(@SortOrder is null)
		Begin
			Select @SortOrder = 1
		End
	end

	-- Create a new Forum
	INSERT INTO 
		cs_Sections (
			Name, 
			Description, 
			ParentID, 
			SettingsID, 
			GroupID, 
			IsModerated, 
			DaysToView, 
			IsActive,
			IsSearchable,
			EnablePostStatistics,
			EnableAutoDelete,
			AutoDeleteThreshold,
			SortOrder,
			ApplicationKey,
			ApplicationType,
			PropertyNames,
			PropertyValues
			)
		VALUES (
			@Name,
			@Description,
			@ParentID,
			@SettingsID,
			@GroupID,
			@IsModerated,
			@DisplayPostsOlderThan,
			@IsActive,
			@IsSearchable,
			@EnablePostStatistics,
			@EnableAutoDelete,
			@AutoDeleteThreshold,
			@SortOrder,
			@ApplicationKey, 
			@ApplicationType,
			@PropertyNames,
			@PropertyValues
			)
	
	SET @SectionID = @@Identity

	If @ApplicationType = 1
	Begin
		Insert cs_weblog_Weblogs (SectionID, SettingsID) Values (@SectionID, @SettingsID)
	End

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_Section_CreateUpdateDelete] to public
go