cs_postcategory_createupdatedelete.prc

解压即可使用

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)='',
	@CategoryType smallint=0,
	@IsEnabled bit=1,
	@ParentID int=0,
	@Description nvarchar(2000)=null,
	@SettingsID int,
	@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 WHERE CategoryID = @CategoryID and SectionID = @SectionID and SettingsID = @SettingsID
	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
	UPDATE cs_Post_Categories SET
		Name = @Name,
		CategoryType = @CategoryType,
		IsEnabled = @IsEnabled,
		ParentID = @ParentID,
		Description = @Description,
		Path = @Path
	WHERE CategoryID = @CategoryID and SectionID = @SectionID and SettingsID = @SettingsID
end
else
begin
	INSERT INTO cs_Post_Categories (SectionID, Name, CategoryType, IsEnabled, ParentID, [Description], [Path], SettingsID)
		VALUES (@SectionID, @Name, @CategoryType, @IsEnabled, @ParentID, @Description, @Path, @SettingsID)
	set @CategoryID = @@IDENTITY
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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