• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

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 = @PallettNo

Open in new window


Hi,

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
0
Zbiebu
Asked:
Zbiebu
  • 2
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
Easiest is to just do the UPDATE, and if no row was found, then do the INSERT:

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

IF @@ROWCOUNT = 0
    INSERT INTO Warehouse.Locations (...)
    VALUES(...)



If @@ROWCOUNT is 0, that means no row was found to UPDATE; that is, the row did not exist, so you INSERT it.
0
 
ZbiebuAuthor Commented:
That's great. Thanks
0
 
Scott PletcherSenior DBACommented:
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.
0
 
ZbiebuAuthor Commented:
Well noted. Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now