Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL 2005 Stored Procedure

Posted on 2009-07-10
3
Medium Priority
?
145 Views
Last Modified: 2012-05-07
I have a SP that Picks Sales O)rder lines and updates the stock with Qty Picked.

If the user picks more than the available stock it then goes negative. Is there an eay way to just set StockQty to zero?
If @PickType = 'PickMan' 
	Begin
		Update OrderDetail Set QtyPicked = @PickQty, Pickedby = @pUser, DatePicked = GETDATE ( )
		Where OrderNo = @OrderNo and Barcode = @Bcode
		set @iMsg = 'Picked ordered qty for ' + @Bcode
 
		-- update the stock qty
		Update Stock Set StockQty = StockQty - isnull((@PickQty),0), 
		QtyReserved = QtyReserved - isnull((@PickQty),0)
		Where Barcode = @Bcode
		goto ShowResults
	End

Open in new window

0
Comment
Question by:HKFuey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 31

Accepted Solution

by:
RiteshShah earned 2000 total points
ID: 24821454
can you do something like this?


 Update Stock Set StockQty = case when StockQty - isnull((@PickQty),0)<0 then 0 else StockQty - isnull((@PickQty),0) end ,
                QtyReserved = QtyReserved - isnull((@PickQty),0)
                Where Barcode = @Bcode
                goto ShowResults
0
 

Author Closing Comment

by:HKFuey
ID: 31601987
That works great, thanks!
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24821601
>>Author Comments:
That works great, thanks!<<

welcome, glad to help!!!

Ritesh Shah

www.SQLHub.com
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

610 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