cs_postcategory_createupdatedelete.prc

community server 源码

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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





CREATE PROCEDURE [dbo].[cs_PostCategory_CreateUpdateDelete]
	@DeleteCategory bit=0,
	@SectionID int=0,
	@Name nvarchar(256)='',
	@IsEnabled bit=1,
	@ParentID int=0,
	@Description nvarchar(2000)=null,
	@SettingsID int,
	@FeaturedPostID int = null,
	@CategoryID int=0 out
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- Are we deleting?
if @DeleteCategory = 1
begin
	DELETE FROM cs_Posts_InCategories WHERE CategoryID = @CategoryID
	DELETE FROM [cs_Post_Categories_Parents] WHERE CategoryID = @CategoryID
	DELETE FROM cs_Post_Categories WHERE CategoryID = @CategoryID and SectionID = @SectionID and SettingsID = @SettingsID
	EXEC dbo.cs_PostCategories_Parents_RebuildIndex @SectionID
	RETURN
end

-- Find out the new path
declare @Path nvarchar(255)
set @Path = isnull((select Path + convert(nvarchar, CategoryID) + '/' from cs_Post_Categories where CategoryID = @ParentID and SectionID = @SectionID and SettingsID = @SettingsID), '/')

-- Are we updating?
if @CategoryID > 0
begin
	IF EXISTS (SELECT CategoryID FROM cs_Post_Categories WHERE Name = @Name AND SectionID = @SectionID AND SettingsID = @SettingsID AND CategoryID <> @CategoryID) BEGIN
		SET @CategoryID = -1
		RETURN
	END

	UPDATE cs_Post_Categories SET
		Name = @Name,
		IsEnabled = @IsEnabled,
		ParentID = @ParentID,
		Description = @Description,
		Path = @Path,
		FeaturedPostID = @FeaturedPostID
	WHERE CategoryID = @CategoryID and SectionID = @SectionID and SettingsID = @SettingsID
end
else
begin
	IF EXISTS (SELECT CategoryID FROM cs_Post_Categories WHERE Name = @Name AND SectionID = @SectionID AND SettingsID = @SettingsID) BEGIN
		SET @CategoryID = -1
		RETURN
	END

	INSERT INTO cs_Post_Categories (SectionID, Name, IsEnabled, ParentID, [Description], [Path], SettingsID, FeaturedPostID)
		VALUES (@SectionID, @Name, @IsEnabled, @ParentID, @Description, @Path, @SettingsID, @FeaturedPostID)
	set @CategoryID = SCOPE_IDENTITY()
end
EXEC dbo.cs_PostCategories_Parents_RebuildIndex @SectionID

GO


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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