Solved

Update statement syntax error

Posted on 2012-03-15
8
332 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 shrink a transaction log file down to a reasonable size.

856 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