Solved

Update statement syntax error

Posted on 2012-03-15
8
329 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
 

Author Comment

by:Zbiebu
ID: 37725292
Hi,

This returns the correct row from the table and the correct values in the variables
Thanks
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
ID: 37725535
If the Qty were NULL, the first UPDATE would not work correctly.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server memory Issue 7 76
Access Migration to Sql Server 2 22
select over clause 1 15
SQL Query 2 0
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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now