Solved

Update statement syntax error

Posted on 2012-03-15
8
333 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
  • 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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Show RTF format in an SSRS report 3 37
Rebooting Witness SQL Server 2 23
Building JSON Results Table FROM DB 9 31
SQL Server Express or Standard? 5 19
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

685 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