Stored Pocedure

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
LVL 7
ayha1999Asked:
Who is Participating?
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
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
 
tim_csConnect With a Mentor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
Good point. Wasn't paying attention :)
0
 
ayha1999Author Commented:
I have already noticed the missing 'NOT'. Is it possible to return 0 if no update occured and 1 for the update?

thanks
0
 
ayha1999Author Commented:
thanks for your help.

ayha
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here