SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE [dbo].cs_Content_CreateUpdateDelete
(
	@ContentID	int out,
	@Action 	int,
	@Name		nvarchar(256),
	@Title		nvarchar(256),
	@Body		ntext,
	@FormattedBody	ntext,
	@Hidden		bit,
	@UpdateDate 	bit,
	@SettingsID 	int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
-- CREATE
IF @Action = 0
BEGIN
	-- first check that content name is not already in use
	IF EXISTS
	(
		SELECT  
			NULL
		FROM 
			cs_Content
		WHERE
			Name = @Name and SettingsID = @SettingsID
	)
	BEGIN
		RAISERROR ( 'Content name in use', 16, 1)
		RETURN
	END				

	DECLARE @SortOrder int

	SELECT @SortOrder = coalesce(MAX(SortOrder) + 1, 1) FROM cs_Content where SettingsID = @SettingsID

	-- Create a new content
	INSERT INTO 
		cs_Content 
		(
			Name,
			Title,
			Body,
			FormattedBody,
			Hidden,
			SortOrder,
			SettingsID
		)
	VALUES 
		(
			@Name,
			@Title,
			@Body,
			@FormattedBody,
			@Hidden,
			@SortOrder,
			@SettingsID
		)
	
	SET @ContentID = SCOPE_IDENTITY()
END


-- UPDATE
ELSE IF @Action = 1
BEGIN

	IF EXISTS
	(
		SELECT  
			NULL
		FROM 
			cs_Content c
		WHERE
			c.Name = @Name
			AND c.ContentID <> @ContentID and c.SettingsID = @SettingsID
	)
	BEGIN
		RAISERROR ( 'Content name in use', 16, 1)
		RETURN
	END				

	IF EXISTS(SELECT NULL FROM cs_Content WHERE ContentID = @ContentID)
	BEGIN
		UPDATE
			cs_Content
		SET
			Name = @Name,
			Title = @Title,
			Body = @Body,
			FormattedBody = @FormattedBody
		WHERE
			ContentID = @ContentID and SettingsID = @SettingsID

		IF @UpdateDate = 1 
			EXEC cs_Content_UpdateDate @ContentID
	END

END

-- DELETE
ELSE IF @Action = 2
BEGIN
	DELETE cs_Content WHERE ContentID = @ContentID and SettingsID = @SettingsID
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_Content_CreateUpdateDelete to public
go