problem about transaction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

problem about transaction

Author

Topic

deadfish
Starting Member

38 Posts

Posted - 2003-05-18 : 07:00:11

I have create some stored procedure as follows:
create procedure sp111
as
begin
...
end

create procedure sp11
as
begin
....
esec sp111
end

create procedure sp12
as
begin
...
end

create procedure sp1
as
begin
.....
exec sp11
exec sp12
end

if there is an error raised in sp12, I have to rollback all transaction in all store procedure. How should I put the 'begin tran' and 'commit tran' in order to achievel this?

Thanks a lot!

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-05-18 : 11:56:40

You must commit or rollback a transaction in its procedure. Why can't you put all statements into one procedure or batch? Maybe post some code here and you'll get some viable solutions.

Sarah Berger MCSD

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-18 : 12:01:34

USE Northwind
GO
DROP TABLE TestTrans
GO
DROP PROCEDURE spOne
GO
DROP PROCEDURE spTwo
GO
DROP PROCEDURE spThree
GO
DROP PROCEDURE spMain
GO
CREATE TABLE TestTrans ([ID] INT)
GO
CREATE PROCEDURE spOne AS
SET NOCOUNT ON
INSERT INTO TestTrans([ID]) VALUES(1)
GO
CREATE PROCEDURE spTwo AS
SET NOCOUNT ON
INSERT INTO TestTrans([ID]) VALUES(2)
GO
CREATE PROCEDURE spThree AS
SET NOCOUNT ON
--This will fail because of datatype
INSERT INTO TestTrans([ID]) VALUES('a')
GO
CREATE PROCEDURE spMain AS
SET NOCOUNT ON
DECLARE @ErrorID INT

BEGIN TRANSACTION
EXEC spOne
IF @@ERROR <> 0 ROLLBACK TRANSACTION
EXEC spTwo
IF @@ERROR <> 0 ROLLBACK TRANSACTION
EXEC spThree
IF @@ERROR <> 0 ROLLBACK TRANSACTION
COMMIT
GO
EXEC spMain
GO
SELECT * FROM TestTrans

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-18 : 12:33:05

More likely

CREATE PROCEDURE spMain AS
SET NOCOUNT ON
DECLARE @ErrorID INT

BEGIN TRANSACTION
EXEC spOne
IF @@ERROR <> 0
begin
ROLLBACK TRANSACTION
raiserror('failed',16,-1)
return
end
EXEC spTwo
IF @@ERROR <> 0
begin
ROLLBACK TRANSACTION
raiserror('failed',16,-1)
return
end
EXEC spThree
IF @@ERROR <> 0
begin
ROLLBACK TRANSACTION
raiserror('failed',16,-1)
return
end
COMMIT
GO
EXEC spMain
GO

And put the same error processing (without the rollback maybe - depends if they also have transaction control) after each sql statement in the subordinate SPs.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

deadfish
Starting Member

38 Posts

Posted - 2003-05-19 : 10:53:24

I've got the idea, thanks everyone! ^____^

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources