April 28, 2009 at 7:11 pm
Hi,
I have three procedures Sprod A, Sprod B, Sprod C.
As
CREATE PROCEDURE A AS
declare @X INT
declare @Y int
BEGIN TRY
BEGIN TRANSACTION
-------------
-------------
COMMIT TRANSACTION
BEGIN CATCH
-------
-----
END CATCH
Similarly I have Sprod B and Sprod C.
The purpose of Sprod A is to insert sales data from Source to Destination, purpose of SprodB is to insert customer data and purpose of Sprod C is to insert location data.
Now, I am planning to create a Sprod D in which I will execute my Sprod A,B,C
and I want to restrict procedure execution in such a way that , to use the return code I don’t want to to execute Sprod B and C if my Sprod A fails.
Some thing like:
create procedure D
AS
Begin
Exec A
End
Begin
Exec B
End
Begin
Exec C
End
-------------( Not sure whether this works ??)
I want this to restrict in a way that , if Procedure A fails I don't want remaining procedures B and C to execute. Process need to stop (Return) if it fails at SprodA.
Can any one please help me how I can achieve this.
Any help is really appreciated. Thanks in advance for all your time.
April 29, 2009 at 5:24 am
You can take advantage of the default return value from procedures like this (untested code):
DECLARE @retval int
EXEC @retval = ProcA @param1='a',@param2='b'
IF @retval = 0
BEGIN
EXEC ProcB
EXEC ProcC
END
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2009 at 11:37 am
Thanks very much fo r the reply.
I did something similar, I have achived it using Return true and Return false.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply