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:
- Check if the passed order number exists within the database, if it does get the order id associated with it.
- Check if the passed part number exists within the database, if it does get the part id associated with it.
- Check if an entry has even been made for this part in this order. If it hasn't create the entry by called the
- If the entry does exists, change the
orderedvalue to the value of
@Quantityand concatenate onto the notes column:
"Quantity updated: [Current Date]Old-[Old Value],New-@Quantity"
- If any If statements return False, exit Procedure returning
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.