Solved

Stored Pocedure

Posted on 2011-03-07
5
252 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
[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
  • 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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