Btukfyl

The homework question:

Write a stored procedure called TransferRegion that accepts a RegionID and a DistributorID. The procedure will transfer a particular region from one distributor to another. When the region is transferred the old distributor will have $1.00 subtracted from their wage and the new distributor will have $1.00 added their wage. Ensure all necessary tables are updated as required.

My question: how do I update the old distributor wage after its been transferred out?

CREATE PROCEDURE TransferRegion 

    (@RegionID INT = NULL, @DistributorID INT = NULL)

AS

    IF @RegionID IS NULL OR @DistributorID IS NULL

    BEGIN

        raiserror('Must provide a region id and distributor id',16,1)

    END

    else

    begin

        begin transaction



        update Region

        set DistributorID = @DistributorID

        where RegionID = @RegionID



        if @@ERROR<> 0

        begin

            raiserror('transfering distributor failed',16,1)

            rollback transaction

        end

        else

        begin

            update Distributor

            set wage = wage + 1

            where DistributorID = @DistributorID



            if @@ERROR<> 0

            begin

                raiserror('transfering distributor failed',16,1)

                rollback transaction

            end

            else

            begin

                commit transaction

            end

        end

    end

    return

You need to find the old distributor currently holding the region before you begin:

DECLARE @oldDist INT = (SELECT distributorid FROM region WHERE regionid = @regionid);

I'm not going to do all the work for you cause this is an academic exercise for your learning benefit, but that above, in the right place coupled with a slight copy paste change to a query you've already written will get you to where you need to be

Always always comment your work when learning

Use comments to form the high level algorithm - very few people think in code, they think in native language (eg English) and translate to code

Write the algorithm in English as comments then leave them in

--check values passed are valid and refer to existing region /distrib; throw a not found error if not



--get the old distributor id we are transferring from-will need this later 



--update to transfer to the new distrib



--update to increment new distrib wage



--update to decrement old distrib wage

That's what you should write first, then put code in below each comment to implement the comment

Other tips:

Use semicolons- omitting them is now deprecated

Don't get into a multiple nested if else if else he'll, its messy and unnecessary; you can do all this work at the base nest level because if you detect an error and raise it the procedure bombs out at that point (if your in a try block the severity needs to be greater than 10 to jump to the catch block)

Do not use RAISERROR - the docs state "new applications should use throw instead". Embarking on a path of using deprecated syntax is not good, discuss with your supervisor if this code skeleton came from them

Consider starting your transaction in a TRY, execute multiple statements and have a CATCH block that checks the @@TRANCOUNT and rolls the transaction back if it's greater than 0