Solved

Update statement syntax error

Posted on 2012-03-15
8
335 Views
Last Modified: 2012-08-13
@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
	
	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 am currently using the above code to insert data into one table and update a further two tables. The insert and first update are working correctly but the second update statement does not work.  I have run the second update statement on its own with valid data and it works fine.  I think it must be a problem with the sytax.
I would be grateful for guidance on this.
Many 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 25

Assisted Solution

by:jogos
jogos earned 250 total points
ID: 37725126
<<but the second update statement does not work.>>
Does nothing Gives error? Does something wrong?
0
 

Author Comment

by:Zbiebu
ID: 37725159
Hi,

No errors are thrown from either SQL or the application using the SP
Thanks
0
 
LVL 25

Expert Comment

by:jogos
ID: 37725181
If Warehouse.Locations.Qty is null (not ZERO) then your + @Qty won't work.
Both your parameters of your procedure and your table don't have leading or trailing spaces (ltrim/rtrim)?

Try your copy procedure with this implemented and exact call as real execution and see what it does.  

select *
FROM Warehouse.Locations
WHERE ProdCode = @ProdCode
AND [Site] = @Site
AND WHouse = @WHouse
AND Aisle = @Aisle
AND Bay = @Bay
AND PallettNo = @PallettNo

select @Qty, @ProdCode , @Site , @WHouse, @Aisle,  @Bay, @PallettNo
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Zbiebu
ID: 37725292
Hi,

This returns the correct row from the table and the correct values in the variables
Thanks
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 37725320
The syntax is fine.

As far as I can tell, you've specified all the key values in the WHERE clause, based on your proc definition.

Check @@ROWCOUNT after the second UPDATE and see if a row was actually UPDATEd or not.

CREATE PROCEDURE ...
    @...
AS
DECLARE @rowcount int

INSERT ...

UPDATE ...

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 @rowcount = @@ROWCOUNT

IF @rowcount = 0 --UPDATE did not actually UPDATE a row
BEGIN
    --add code here as needed
END --IF
0
 
LVL 25

Expert Comment

by:jogos
ID: 37725477
<<This returns the correct row from the table and the correct values in the variables>>
So the update must take place (how to check see ScottPletcher)

Did you concidered this remark?
If Warehouse.Locations.Qty is null (not ZERO) then your + @Qty won't work.


SET Warehouse.Locations.Qty = IsNull(Warehouse.Locations.Qty,0) + @Qty
0
 

Author Comment

by:Zbiebu
ID: 37725495
Hi,

I have run the sp from the MMS, providing valued data and the @rowcount returns 1 row updated. So I'll go back to my VB code and check there because using the app, it does not update.  Thanks to both for you comments
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37725535
If the Qty were NULL, the first UPDATE would not work correctly.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

691 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