Update statement syntax error

@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
ZbiebuAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
jogosConnect With a Mentor Commented:
<<but the second update statement does not work.>>
Does nothing Gives error? Does something wrong?
0
 
ZbiebuAuthor Commented:
Hi,

No errors are thrown from either SQL or the application using the SP
Thanks
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
jogosCommented:
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
 
ZbiebuAuthor Commented:
Hi,

This returns the correct row from the table and the correct values in the variables
Thanks
0
 
jogosCommented:
<<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
 
ZbiebuAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
If the Qty were NULL, the first UPDATE would not work correctly.
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.

All Courses

From novice to tech pro — start learning today.