SP_how_to_join

CREATE PROCEDURE [dbo].[insert_order_2]
    @ShipperID uniqueidentifier,
    @PaymentTypeID uniqueidentifier,
    @ShipperName nvarchar(50),
    @PaymentTypeName nvarchar(20),
    @CustEmail nvarchar(MAX),
    @DeliveryNotes nvarchar(MAX),
    @CustID uniqueidentifier
AS
BEGIN
    UPDATE Orders
    SET ShipperID = @ShipperID,
        PaymentTypeID = @PaymentTypeID,
        ShipperName =
            (SELECT Shippers.ShipperName
                FROM Orders
                INNER JOIN Shippers
                ON Orders.ShipperID=Shippers.ShipperID
                AS @ShipperName),
        PaymentTypeName =
            (SELECT PaymentTypes.PaymentTypeName
                FROM Orders
                INNER JOIN PaymentTypes
                ON Orders.PaymentTypeID=PaymentTypes.PaymentTypeID
                AS @PaymentTypeName),
        CustEmail = @CustEmail,
        DeliveryNotes = @DeliveryNotes
    WHERE CustID = @CustID
END