Solved

Evaluating values

Posted on 2012-03-23
4
241 Views
Last Modified: 2012-06-27
@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
Comment
Question by:Zbiebu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37757640
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
 

Author Closing Comment

by:Zbiebu
ID: 37757757
That's great. Thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37757787
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
 

Author Comment

by:Zbiebu
ID: 37757821
Well noted. Thanks again.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question