Solved

Stored Pocedure

Posted on 2011-03-07
5
210 Views
Last Modified: 2012-05-11
How can I execute all the three sql statments below in single stored procedure?  if the first statement is returned no row, then only I want to execute the second and third statments.

SELECT a.* FROM Table1 a INNER JOIN Table2 b
            ON a.PartNo = b.PartNo and a.StoreID = b.StoreID Where b.QtyIn >
            a.Stock and b.Id=@Id

       UPDATE Table1 SET Stock = P.Stock - PI.QtyIn from
            Table1 as P Inner Join Table2 as PI on P.PartNo=PI.PartNo and
            P.StoreID = PI.StoreID WHERE PI.Id=@Id

       delete from Table2 where Id=@id

thanks

ayha
0
Comment
Question by:ayha1999
  • 2
  • 2
5 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 200 total points
ID: 35057339
You could use something like:
IF EXISTS(
     SELECT a.* FROM Table1 a INNER JOIN Table2 b 
            ON a.PartNo = b.PartNo and a.StoreID = b.StoreID Where b.QtyIn > 
            a.Stock and b.Id=@Id)
  BEGIN

       UPDATE Table1 SET Stock = P.Stock - PI.QtyIn from
            Table1 as P Inner Join Table2 as PI on P.PartNo=PI.PartNo and 
            P.StoreID = PI.StoreID WHERE PI.Id=@Id

       delete from Table2 where Id=@id
   END

Open in new window

Then the second and third statements will only run if the first statement returns a row.
0
 
LVL 15

Assisted Solution

by:tim_cs
tim_cs earned 50 total points
ID: 35057407
Since you want to fire of the second and third if there are no results for the first then you could try out carl_tawn's example only with IF NOT EXISTS.  
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35057420
Good point. Wasn't paying attention :)
0
 
LVL 7

Author Comment

by:ayha1999
ID: 35057575
I have already noticed the missing 'NOT'. Is it possible to return 0 if no update occured and 1 for the update?

thanks
0
 
LVL 7

Author Closing Comment

by:ayha1999
ID: 35058074
thanks for your help.

ayha
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
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.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

17 Experts available now in Live!

Get 1:1 Help Now