DROP PROCEDURE TransferRegion
GO
CREATE PROCEDURE TransferRegion (@RegionID int,  @DistributorID int)
AS
	-- Turn Off System Output
	SET NOCOUNT ON

	-- Declare a few useful variables
	DECLARE
		@OutputMsg varchar(255),
		@CurrentDistributorID int
	BEGIN
		-- Load our Parameters with a clean version of themselves
		SET @RegionID = ltrim(rtrim(@RegionID))
		SET @DistributorID = ltrim(rtrim(@DistributorID))

		-- Check for null values
		IF @RegionID is not null
			BEGIN
				IF @DistributorID is not null
					BEGIN
						-- Resolve Current Distributor
						SELECT	@CurrentDistributorID = DistributorID
						FROM  Region
						WHERE RegionID = @RegionID

						IF @CurrentDistributorID is not null
							BEGIN
								IF @CurrentDistributorID <> @DistributorID
									BEGIN
										-- Transactions should not occur until
										-- we actually have something to change
										-- At this point we have proved that it
										-- is time to begin altering data so we
										-- should start a new transaction.
										BEGIN TRANSACTION
											-- Transfer the Region
											UPDATE Region
											SET DistributorID = @DistributorID
											WHERE DistributorID = @CurrentDistributorID

											-- Check For Error
											IF @@Error = 0
												BEGIN
													-- Alter the Old Distributors Wages
													UPDATE Distributor
													SET Wage = Wage - 1
													WHERE DistributorID = @CurrentDistributorID

													-- Check for Error
													IF @@Error = 0
														BEGIN
															-- Alter the Wage of the new Distributor
															UPDATE Distributor
															SET Wage = Wage + 1
															WHERE DistributorID = @DistributorID

															-- Check for Error
															IF @@Error = 0
																BEGIN
																	-- Output Success Message
																	SET @OutputMsg = 'The Region has Successfully been Transferred'
																	COMMIT TRANSACTION
																END
															ELSE
																BEGIN
																	-- Kill this entire Transaction and Procedure as this error is not due to poor data
																	RAISERROR('There was a problem updating the old distributors wage', 16, 1)
																	ROLLBACK TRANSACTION
																END
															-- endif
														END
													ELSE
														BEGIN
															-- Kill this entire Transaction and Procedure as this error is not due to poor data
															RAISERROR('There was a problem updating the old distributors wage', 16, 1)
															ROLLBACK TRANSACTION
														END
													-- endif
												END
											ELSE
												BEGIN
													-- Kill this entire Transaction and Procedure as this error is not due to poor data
													RAISERROR('There were errors transferring the Region', 16, 1)
													ROLLBACK TRANSACTION
												END
											-- endif
									END
								ELSE
									BEGIN
										SET @OutputMsg = 'No updates were made as the new DistributorID is equal to the existing DistributorID'
									END	
								-- endif
							END
						ELSE
							BEGIN
								-- Output the user errors
								SET @OutputMsg = 'The @DistributorID should not be equal to the current DistributorID'
							END
						-- endif
					END
				ELSE
					BEGIN
						-- Output the user errors
						SET @OutputMsg = 'The @DistributorID Cannot be blank'
					END
				-- endif
			END
		ELSE
			BEGIN
				-- Output the user errors
				SET @OutputMsg = 'The @RegionID Cannot be blank'
			END
		-- endif
		-- Physically output any Message that are not Exception-Type Messages
		IF @OutputMsg is not null
			BEGIN
				PRINT @OutputMsg
			END
	END
GO