Solved

SQL 2005 Stored Procedure

Posted on 2009-07-10
3
137 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
  • 2
3 Comments
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Copy Database Wizard 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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

772 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