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) ASSET 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 ); ENDEND TRYBEGIN CATCH SELECT @ErrorDesc = N'Error validating input paramaters' , @RetVal = -1; GOTO ErrorHandlerEND CATCHBEGIN 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; ENDEND TRYBEGIN CATCH SELECT @ErrorDesc = N'Error reading Post' , @RetVal = -1; GOTO ErrorHandlerEND CATCH;/****************************************************** Commit Transactions*****************************************************/CommitTransaction:IF XACT_STATE() = 1BEGIN COMMIT TRANSACTION;END/*****************************************************/ErrorHandler:/*****************************************************/IF @RetVal != 0BEGIN /**************************************************** ** 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 OUTPUTENDRETURN @RetVal;