Solved

Stored Pocedure

Posted on 2011-03-07
5
235 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 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