Solved

Stored Pocedure

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

696 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