CREATE PROCEDURE [Blog].[Post_Read_By_BlogID] (
	@BlogID				int
,	@PageSize			int				-- If either @PageSize or @PageNumber is 0 all posts will
,	@PageNumber			int				-- be returned.
,	@ErrorID			int OUTPUT
) AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

/*****************************************************/
BEGIN TRANSACTION;
/*****************************************************/

/****************************************************
** Declare local variables
*****************************************************/
DECLARE
	@ErrorDesc		nvarchar(256)
,	@RetVal			int

/****************************************************
** Validate input paramaters
*****************************************************/
BEGIN TRY
	IF @BlogID IS NULL 
	BEGIN
		RAISERROR (
			N'@BlogID must not be null'
		,	15
		,	1
		);
	END
	
	IF NOT EXISTS(SELECT 1 FROM [Blog].[Blog] WHERE [BlogID] = @BlogID)
	BEGIN
		SELECT
			@ErrorDesc = N'@BlogID: ' + CAST(@BlogID AS nvarchar) + ' does not exist'

		RAISERROR (
			@ErrorDesc
		,	15
		,	1
		);
		
	END

	IF @PageSize IS NULL 
	BEGIN
		RAISERROR (
			N'@PageSize must not be null'
		,	15
		,	1
		);
	END

	IF @PageSize < 0 
	BEGIN
		RAISERROR (
			N'@PageSize must be a positive integer, pass 0 to return all posts as a single page'
		,	15
		,	1
		);
	END

	IF @PageNumber IS NULL 
	BEGIN
		RAISERROR (
			N'@PageNumber must not be null'
		,	15
		,	1
		);
	END

	IF @PageNumber < 0 
	BEGIN
		RAISERROR (
			N'@PageNumber must be a positive integer, pass 0 to return all posts as a single page'
		,	15
		,	1
		);
	END

END TRY
BEGIN CATCH
	SELECT
		@ErrorDesc	= N'Error validating input paramaters'
	,	@RetVal		= -1;
	
	GOTO ErrorHandler
END CATCH

BEGIN TRY
	IF @PageSize = 0 OR @PageNumber = 0
	BEGIN
		/****************************************************
		** Retrieve all posts
		*****************************************************/
		SELECT
			p.[PostID]			
		,	p.[CurrentRevisionID]	
		,	p.[ParentPostID]		
		,	p.[PostTitle]			
		,	p.[PostDesc]	
		,	cr.[RevisionText]		
		,	p.[PostCreationDate]	
		,	p.[PostLastUpdateDate]
		FROM
			[Blog].[Post] p
			JOIN [Core].[Revision] cr ON (p.[CurrentRevisionID] = cr.[RevisionID])
		WHERE
			p.[PostIsDisplayed] = 1
		AND
			p.[BlogID] = @BlogID
		ORDER BY
			[PostCreationDate] ASC;
	END
	ELSE
	BEGIN
		/****************************************************
		** Retrieve all posts for the specified page number
		*****************************************************/
		WITH [CTE] AS (
			SELECT
				ROW_NUMBER() OVER (ORDER BY	[PostCreationDate] ASC) AS [RowNumber]
			,	[PostID]
			FROM
				[Blog].[Post] p
			WHERE
				p.[PostIsDisplayed] = 1
			AND
				p.[BlogID] = @BlogID		
		)
		SELECT
			p.[PostID]			
		,	p.[CurrentRevisionID]	
		,	p.[ParentPostID]		
		,	p.[PostTitle]			
		,	p.[PostDesc]	
		,	cr.[RevisionText]		
		,	p.[PostCreationDate]	
		,	p.[PostLastUpdateDate]
		FROM
			[CTE]
			JOIN [Blog].[Post] p ON ([CTE].[PostID] = p.[PostID])
			JOIN [Core].[Revision] cr ON (p.[CurrentRevisionID] = cr.[RevisionID])
		WHERE
			p.[PostIsDisplayed] = 1
		AND
			p.[BlogID] = @BlogID
		AND
			[CTE].[RowNumber] BETWEEN (@PageSize * (@PageNumber - 1)) + 1 AND (@PageNumber * @PageSize)
		ORDER BY
			[PostCreationDate] ASC;
	END
END TRY
BEGIN CATCH
	SELECT
		@ErrorDesc	= N'Error reading Post'
	,	@RetVal		= -1;
	
	GOTO ErrorHandler
END CATCH;

/****************************************************
** Commit Transactions
*****************************************************/
CommitTransaction:
IF XACT_STATE() = 1
BEGIN
	COMMIT TRANSACTION;
END

/*****************************************************/
ErrorHandler:
/*****************************************************/
IF @RetVal != 0
BEGIN
	/****************************************************
	** Declare variables to hold error information
	*****************************************************/
	DECLARE
		@ErrorMessage	nvarchar(4000)	
	,	@ErrorProcedure	nvarchar(126)	
	,	@ErrorLine		int				
	,	@ErrorSeverity	int				
	,	@ErrorNumber	int 			
	,	@ErrorState		int				
	
	/****************************************************
	** Retrieve error information
	*****************************************************/
	SELECT
		@ErrorMessage	= ERROR_MESSAGE()
	,	@ErrorProcedure	= ERROR_PROCEDURE()
	,	@ErrorLine		= ERROR_LINE()
	,	@ErrorSeverity	= ERROR_SEVERITY()
	,	@ErrorNumber	= ERROR_NUMBER()
	,	@ErrorState		= ERROR_STATE()
	
	/*****************************************************/
	RollbackTransaction:
	/*****************************************************/
	IF XACT_STATE() != 0
	BEGIN
		ROLLBACK TRANSACTION;
	END
	
	/****************************************************
	** Log the error
	*****************************************************/
	EXEC [Core].[ErrorSQL_Create] 
		@ErrorDesc		= @ErrorDesc			
	,	@ErrorMessage	= @ErrorMessage
	,	@ErrorProcedure	= @ErrorProcedure
	,	@ErrorLine		= @ErrorLine
	,	@ErrorSeverity	= @ErrorSeverity
	,	@ErrorNumber	= @ErrorNumber
	,	@ErrorState		= @ErrorState
	,	@NewErrorID		= @ErrorID			OUTPUT
END

RETURN @RetVal;