DROP PROCEDURE TransferRegionGOCREATE 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 ENDGO