Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.
Solved
Stored Pocedure
Posted on 2011-03-07
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
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
[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
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
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.
I have already noticed the missing 'NOT'. Is it possible to return 0 if no update occured and 1 for the update?
thanks
thanks
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month10 days, 22 hours left to enroll
618 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.