Solved

Evaluating values

Posted on 2012-03-23
4
236 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
  • 2
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now