/****** Object:  StoredProcedure [updateContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [updateContent];
GO
	
CREATE PROCEDURE [dbo].[updateContent]

(
	@ContentID int,
	@Number nvarchar(256)=null,
	@Text nvarchar(MAX)=null,
	@Type int=null,
	@FormatID int=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@LastChanged timestamp,
	@newLastChanged timestamp output
)
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	UPDATE [Contents]
		SET
			[Number]=@Number,
			[Text]=@Text,
			[Type]=@Type,
			[FormatID]=@FormatID,
			[Config]=@Config,
			[DTS]=@DTS,
			[UserID]=@UserID
		WHERE [ContentID]=@ContentID AND [LastChanged]=@LastChanged
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [Contents] WHERE [ContentID]=@ContentID)
			RAISERROR('Content record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('Content has been edited by another user', 16, 1)            	
	END

		SELECT 		@newLastChanged=[LastChanged]	
		FROM [Contents] WHERE [ContentID]=@ContentID		

	IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
	IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
 	ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit.  Top level will rollback
	EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateContent Succeeded'
ELSE PRINT 'Procedure Creation: updateContent Error on Creation'
GO

/****** Object:  StoredProcedure [addContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
    DROP PROCEDURE [addContent];
GO
	
CREATE PROCEDURE [dbo].[addContent]

(
	@Number nvarchar(256)=null,
	@Text nvarchar(MAX)=null,
	@Type int=null,
	@FormatID int=null,
	@Config nvarchar(MAX)=null,
	@DTS datetime,
	@UserID nvarchar(100),
	@newContentID int output,
	@newLastChanged timestamp output
)
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	INSERT INTO [Contents]
	(
		[Number],
		[Text],
		[Type],
		[FormatID],
		[Config],
		[DTS],
		[UserID]
	)
	VALUES
	(
		@Number,
		@Text,
		@Type,
		@FormatID,
		@Config,
		@DTS,
		@UserID
	)
	SELECT @newContentID= SCOPE_IDENTITY()
	SELECT 		@newLastChanged=[LastChanged]
		FROM [Contents] WHERE [ContentID]=@newContentID
	IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
	IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
 	ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit.  Top level will rollback
	EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addContent Succeeded'
ELSE PRINT 'Procedure Creation: addContent Error on Creation'
GO