Insert Statement

Hi Friends,
In my SP i have 3 insert statements that inserts record into 3 different
tables. If any of the inserts fail, I want to roll back any other inserts
that is executed. how to do this. please give me an example.
example:
insert into abc values('ert','ert')
insert into xyz values('rtert','rtyrty')
if the insert operation fails for the table xyz then the insert for abc
should not be commited.
thnks
vanithaput your sql statement in a transaction
begin transaction
insert 1....
insert 2....
insert 3.......
commit transaction
<hr>
MCP #2324787
"Vanitha" wrote:

> Hi Friends,
> In my SP i have 3 insert statements that inserts record into 3 different
> tables. If any of the inserts fail, I want to roll back any other inserts
> that is executed. how to do this. please give me an example.
> example:
> insert into abc values('ert','ert')
> insert into xyz values('rtert','rtyrty')
> if the insert operation fails for the table xyz then the insert for abc
> should not be commited.
> thnks
> vanitha|||CREATE PROCC dbo.ProcedureName
(paramlist)
AS
BEGIN
BEGIN TRAN [tranname]
INSERT INTO First table ...
IF @.@.ERROR > 0
BEGIN
ROLLBACK TRAN [tranname]
RETURN [Errorcode]
END
INSERT INTO second table ...
IF @.@.ERROR > 0
BEGIN
ROLLBACK TRAN [tranname]
RETURN [Errorcode]
END
INSERT INTO third table ...
IF @.@.ERROR > 0
BEGIN
ROLLBACK TRAN [tranname]
RETURN [Errorcode]
END
COMMIT TRAN [tranname]
END
GO
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:08B477A8-5335-45EB-BAC2-B76B2B2A5563@.microsoft.com...
> Hi Friends,
> In my SP i have 3 insert statements that inserts record into 3 different
> tables. If any of the inserts fail, I want to roll back any other inserts
> that is executed. how to do this. please give me an example.
> example:
> insert into abc values('ert','ert')
> insert into xyz values('rtert','rtyrty')
> if the insert operation fails for the table xyz then the insert for abc
> should not be commited.
> thnks
> vanitha|||This involves error handling as well as transaction handling. It is a large
topic, so some
background reading will sort this out for you. The best reference to this to
pic, IMO, is the error
handling articles at:
http://www.sommarskog.se/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:08B477A8-5335-45EB-BAC2-B76B2B2A5563@.microsoft.com...
> Hi Friends,
> In my SP i have 3 insert statements that inserts record into 3 different
> tables. If any of the inserts fail, I want to roll back any other inserts
> that is executed. how to do this. please give me an example.
> example:
> insert into abc values('ert','ert')
> insert into xyz values('rtert','rtyrty')
> if the insert operation fails for the table xyz then the insert for abc
> should not be commited.
> thnks
> vanitha|||Just adding BEGIN TRAN and COMMIT TRAN will not cut it. The first might be O
K, the second fail and
the third OK. So the first and the third are performed but not the second. Y
ou need error handling
as well (or SET XACT_ABORT ON, but almost no-one in the SQL Server community
uses this setting).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:80DA1215-F68E-4FA3-BE39-3B08BD39473A@.microsoft.com...
> put your sql statement in a transaction
>
> begin transaction
> insert 1....
> insert 2....
> insert 3.......
> commit transaction
>
> --
>
> <hr>
> MCP #2324787
>
> "Vanitha" wrote:
>|||Thanks
"Tibor Karaszi" wrote:

> This involves error handling as well as transaction handling. It is a larg
e topic, so some
> background reading will sort this out for you. The best reference to this
topic, IMO, is the error
> handling articles at:
> http://www.sommarskog.se/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
> news:08B477A8-5335-45EB-BAC2-B76B2B2A5563@.microsoft.com...
>