help with stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with stored procedure

Author

Topic

camyden
Starting Member

9 Posts

Posted - 2007-01-03 : 02:59:51

Greetings!

I have this stored procedure

UPDATE Inventory
SET InStock = Inventory.InStock - InvoiceDetails.QTY
FROM Inventory INNER JOIN
InvoiceDetails ON Inventory.ItemNo = InvoiceDetails.ItemNumber;
SELECT ItemNo, ItemDescription, InStock, Units, Cost, ItemID FROM Inventory WHERE (Itemno = @Itemno)

Problem is, it subtracts qty's from rows that are not selected. Any help figuring out how to set the filter on this to only affect the selected rows would be greatly appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-03 : 03:09:07

Perhaps missing of where clause in the update statement ?


UPDATE Inventory
SET    InStock = Inventory.InStock - InvoiceDetails.QTY
FROM   Inventory INNER JOIN InvoiceDetails 
       ON Inventory.ItemNo = InvoiceDetails.ItemNumber
WHERE (Itemno = @Itemno) 

SELECT ItemNo, ItemDescription, InStock, Units, Cost, ItemID FROM Inventory WHERE (Itemno = @Itemno)

camyden
Starting Member

9 Posts

Posted - 2007-01-03 : 03:10:38

Sorry,
Left that line out in the question, yet it is in the procedure.

Using VB Express so
InvoiceTableAdapter.UpdateQuery1("ItemNo") runs but creates the problem.

If I use
Where (InvoiceID=@InvoiceId)

get error "cannot convert string to int"
invoicetableadapter.updatequery1("InvoiceID")

Kristen
Test

22859 Posts

Posted - 2007-01-03 : 03:27:34

"Problem is, it subtracts qty's from rows that are not selected"

Sounds like its selecting some rows that you don't intend it to. I normally debug these type of cases with:


BEGIN TRANSACTION
DECLARE @Itemno int
SET @Itemno = 1234
SELECT 'Before',ItemNo, ItemDescription, InStock, Units, Cost, ItemID FROM Inventory WHERE (Itemno = @Itemno) 
SELECT U.Itemno, InvoiceDetails.QTY, [OLD_InStock] = U.InStock, [NEW_InStock] =
-- UPDATE U SET InStock 
                     = U.InStock - InvoiceDetails.QTY
FROM Inventory AS U
INNER JOIN InvoiceDetails 
ON Inventory.ItemNo = InvoiceDetails.ItemNumber
WHERE U.Itemno = @Itemno
SELECT 'After',ItemNo, ItemDescription, InStock, Units, Cost, ItemID FROM Inventory WHERE (Itemno = @Itemno)
ROLLBACK

Start off with the bit in Red, when that is showing only what you expect to be updated comment out the SELECT, and comment IN the UPDATE, and then review the Before / After values to check they are as expected.

There might be something else changing stuff - like a trigger, for example, but you can diagnose that in the AFTER data.

Kristen

camyden
Starting Member

9 Posts

Posted - 2007-01-03 : 03:32:02

FABULOUS!

I'll try this.

Thanks

camyden
Starting Member

9 Posts

Posted - 2007-01-03 : 04:02:02

Help

The Declare cursor SQL construct or statement is not supported.

Kristen
Test

22859 Posts

Posted - 2007-01-03 : 04:04:02

Ah ... you need to try this from Query Analyser, or similar, not from (for example) a VB application.

The SQL above is stand alone - i.e. it will ROLLBACK any changes it makes; so ... you can run it over-and-over until it is doing the right job. But you need to run it in some SQL query tool that will just display the results to you.

Kristen

camyden
Starting Member

9 Posts

Posted - 2007-01-03 : 04:22:00

Kristen, I'm lost

was able to run parts of your example in "query builder" but some of it was throwing errors.

I'm not sure this is the route I should take since the only way I know how to call a procedure is from

datatableadapter.update"queryname"(parameter)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 04:51:37

No, you must make that code as a stored procedure in the database, and then call the stored procedure from the vb application. The @ItemNo is parameter and should not be declare explicit in the code.

Peter Larsson
Helsingborg, Sweden

camyden
Starting Member

9 Posts

Posted - 2007-01-03 : 15:44:48

Maybe I'm just not understanding the stored procedure. I use the Querybuilder in vbexpress to create the stored procedure. If I use a where clause, vbexpress will not let me call tableadapterupdate without the parameter. It will say "Argument not specified for ItemID"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 15:48:29

Is this homework or part of your job description?

If you do not understand the concept of SP, and try to run everything dynamically, you should consider changing career path.

Peter Larsson
Helsingborg, Sweden

camyden
Starting Member

9 Posts

Posted - 2007-01-03 : 15:55:20

I'll take that into consideration. No, this is not homework. I'm building this app for a local business/friend.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources