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
    DECLARE @OrderID int, @PartID int

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

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

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

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.

  • how about some code refactoring on your code?
    – Biju jose
    Commented Aug 1, 2018 at 7:15

1 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().

  • 1
    you are correct for string addition. However, using the actual CONCAT (SQL 2012+) function will set NULL values to empty strings. Commented Jul 31, 2018 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.