Zbiebu
asked on
Evaluating values
@ProdCode VARCHAR(10),
@Qty INT,
@RequestedBy VARCHAR(15),
@Site VARCHAR(20),
@WHouse VARCHAR(15),
@Aisle VARCHAR(10),
@Bay VARCHAR(10),
@PallettNo VARCHAR(10),
@InOut VARCHAR(5),
@Comments VARCHAR(255)
AS
DECLARE @Rowcount INT
SET NOCOUNT ON;
INSERT INTO Warehouse.Movements
(ProdCode, Qty, MoveDate, RequestedBy, [Site],WHouse,Aisle,Bay, PallettNo, InOut,
Comments)
VALUES(@ProdCode,@Qty,GETDATE(), @RequestedBy,@Site,@WHouse,@Aisle,@Bay, @PallettNo,
@InOut,@Comments)
UPDATE Warehouse.Products
SET Warehouse.Products.Quantity = Warehouse.Products.Quantity + @Qty
WHERE Warehouse.Products.ProdCode = @Prodcode
UPDATE Warehouse.Locations
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 = @PallettNoHi,
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.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome.
NOTE though:
The check of @@ROWCOUNT must come IMMEDIATELY after the UPDATE; NO other statements can occur in between.
For example, this would not be valid:
UPDATE Warehouse.Locations
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
SET @error = @@ERROR --<<-- resets @@ROWCOUNT, UPDATE value is now LOST
IF @@ROWCOUNT = 0 --<<-- will always be 1 because of the SET
INSERT INTO Warehouse.Locations (...)
VALUES(...)
So, think of the IF @@ROWCOUNT as being directly paired with UPDATE.
NOTE though:
The check of @@ROWCOUNT must come IMMEDIATELY after the UPDATE; NO other statements can occur in between.
For example, this would not be valid:
UPDATE Warehouse.Locations
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
SET @error = @@ERROR --<<-- resets @@ROWCOUNT, UPDATE value is now LOST
IF @@ROWCOUNT = 0 --<<-- will always be 1 because of the SET
INSERT INTO Warehouse.Locations (...)
VALUES(...)
So, think of the IF @@ROWCOUNT as being directly paired with UPDATE.
ASKER
Well noted. Thanks again.
ASKER