Solved

Evaluating values

Posted on 2012-03-23
4
242 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

632 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