giveindia
asked on
Rolling back a sequence of stored procedures
I have a .NET console application that runs a sequence of 26 stored procedures. I was wondering if there was a way to commit all the stored procedures only if all 26 execute correctly. If any of them fails I would like to roll back all the 26 stored procedures.
How can I go about doing this,
Aditya
How can I go about doing this,
Aditya
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Éric Moreau
your 26 SPs are all doing updates? You might be caught with locks!
ASKER
Against different tables.
Thanks,
Aditya
Thanks,
Aditya
have you tried Pawan suggestions? It is most probably the only way to chain your SPs.
ASKER
Yes will be implementing Pawan's suggestion.
Thanks,
Aditya
Thanks,
Aditya
ASKER
Tried the following, on SQLServer,
BEGIN TRY
EXEC sp_A
EXEC sp_B
EXEC sp_B
EXEC sp_D
EXEC sp_E
EXEC sp_F
EXEC sp_G
EXEC sp_H
EXEC sp_I
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
Encountered the following errors on Executing:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
AND
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
BEGIN TRY
EXEC sp_A
EXEC sp_B
EXEC sp_B
EXEC sp_D
EXEC sp_E
EXEC sp_F
EXEC sp_G
EXEC sp_H
EXEC sp_I
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
Encountered the following errors on Executing:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
AND
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
you need a "BEGIN TRANSACTION" between Begin Try and your first Exec
Hi,
Is that worked? Do you have more questions?
Regards,
Pawan
Is that worked? Do you have more questions?
Regards,
Pawan
ASKER
Just one more question. If I want to return the number of rows affected for each procedure how do I go about doing it ?
Thanks,
Aditya
Thanks,
Aditya
Use below in your stored procedures...
If you multiple dml operations apart from select then everytime you have save @@rowcount value in a new variable.
DECLARE @ReturnVariable AS INT = 0
SET @ReturnVariable = @@ROWCOUNT
RETURN @ReturnVariable
Hope it helps !!
If you multiple dml operations apart from select then everytime you have save @@rowcount value in a new variable.
DECLARE @ReturnVariable AS INT = 0
SET @ReturnVariable = @@ROWCOUNT
RETURN @ReturnVariable
Hope it helps !!
ASKER
So for the example given below how do I return the number of rows for each procedure.
BEGIN TRY
BEGIN TRANSACTION
EXEC sp_A
EXEC sp_B
EXEC sp_B
EXEC sp_D
EXEC sp_E
EXEC sp_F
EXEC sp_G
EXEC sp_H
EXEC sp_I
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
Thanks,
Aditya
BEGIN TRY
BEGIN TRANSACTION
EXEC sp_A
EXEC sp_B
EXEC sp_B
EXEC sp_D
EXEC sp_E
EXEC sp_F
EXEC sp_G
EXEC sp_H
EXEC sp_I
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
Thanks,
Aditya
Sample for you
Child Stored procedure..
This the main procedure where we call all the procs
Output
Hope it helps !!
Child Stored procedure..
CREATE PROC PawanXX
(
@a INT
,@b INT OUTPUT
)
AS
BEGIN
SELECT TOP 2 * FROM X
SET @b = @@ROWCOUNT
RETURN @b
END
GOThis the main procedure where we call all the procs
DECLARE @RC int
DECLARE @a int
DECLARE @b int
EXECUTE @RC = [dbo].[PawanXX]
@a
,@b OUTPUT
SELECT @RCOutput
ProcessName Parent Child
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
ShareDrafts Job12 Job03
ShareDrafts Job13 Job58
(2 row(s) affected)
-----------
2
(1 row(s) affected)Hope it helps !!