DECLARE @Rowcount INT
SET NOCOUNT ON;
INSERT INTO Warehouse.Movements
(ProdCode, Qty, MoveDate, RequestedBy, [Site],WHouse,Aisle,Bay, PallettNo, InOut,
VALUES(@ProdCode,@Qty,GETDATE(), @RequestedBy,@Site,@WHouse,@Aisle,@Bay, @PallettNo,
SET Warehouse.Products.Quantity = Warehouse.Products.Quantity + @Qty
WHERE Warehouse.Products.ProdCode = @Prodcode
SET Warehouse.Locations.Qty = Warehouse.Locations.Qty + @Qty
WHERE ProdCode = @ProdCode
AND [Site] = @Site
AND WHouse = @WHouse
AND Aisle = @Aisle
AND Bay = @Bay
AND PallettNo = @PallettNo
I use the code above to insert into one table and update 2 others. Before the second update, I would like to be able to check for existing rows in the Locations table. If a row does not exist, to be able to insert it. Otherwise update it.
So if Prodcode <> @Prodcode
AND Site <> @Site
AND Warehouse <> @WHouse
AND Aisle <>@aisle
AND Bay <> @Bay
AND PallettNo <> @PalletNo
If all of these result in no match, then I would like to Insert the details. If a match is found, to update accordingly.
Is there a way to do this in my SP. Any guidance would be appreciated.