Concatenate into Field in Update

Question

I am trying to write a Stored Procedure which updates a quantity data column in one of my tables, and then takes a field set for random notes and concatenates the update information.

This is what I have for the Procedure thus far

CREATE PROCEDURE UpdatePartQuantity(
    @OrderNum VARCHAR(20),
    @PartNum  VARCHAR(100),
    @Quantity INT
) AS
BEGIN
    DECLARE @OrderID int, @PartID int

    IF(EXISTS(SELECT order_id FROM purchase_orders WHERE order_number = @OrderNum))
    BEGIN
        SELECT @OrderID = order_id
          FROM purchase_orders
         WHERE order_number = @OrderNum
    END
    ELSE
        RETURN 1

    IF(EXISTS(SELECT part_id FROM parts WHERE part_number = @PartNum))
    BEGIN
        SELECT @PartID = part_id
          FROM parts
         WHERE part_number = @PartNum
    END
    ELSE
        RETURN 1

    IF(EXISTS(SELECT part_id, order_id FROM order_parts WHERE order_id = @OrderID AND part_id = @PartID))
    BEGIN
        UPDATE order_parts
        SET ordered = @Quantity, notes = CONCAT(notes, 'string goes here')
        WHERE order_id = @OrderID AND part_id = @PartID
        RETURN 0
    END
    ELSE
        EXEC AddPartToOrder @OrderNum, @PartNum, @Quantity
END

So here is how the procedure is suppose to run:

1. Check if the passed order number exists within the database, if it does get the order id associated with it.
2. Check if the passed part number exists within the database, if it does get the part id associated with it.
3. Check if an entry has even been made for this part in this order. If it hasn't create the entry by called the AddPartToOrder Procedure
4. If the entry does exists, change the ordered value to the value of @Quantity and concatenate onto the notes column: "Quantity updated: [Current Date]Old-[Old Value],New-@Quantity"
5. If any If statements return False, exit Procedure returning 1

My issue is I am not sure how to set up my CONCAT statement. How would I put a GETDATE call inside of it? How would I reference the value of @Quantity? I am not exactly sure how to approach this.

Answer 1

You can concatenate text with text, so you need to CAST(). For example,

notes = CAST(GETDATE() AS varchar(max)) + 'Old-' + CAST(ISNULL(notes, '') AS varchar(max)) + 'New-' + CAST(@Quantity AS varchar(max))

Note that concatenate null with something will give you null, so to implement CONCAT() function's functionality you need ISNULL().