| Author |
 Topic  |
|
|
sent_sara
Constraint Violating Yak Guru
India
328 Posts |
 Posted - 11/29/2006 : 05:04:39
|
hi fys can any people help regarding the below procedure
CREATE procedure hrd_emp
as
begin tran
insert into emp(idno,name) values('first','se')
commit tran
begin tran
insert into t(name) values('first')
commit tran
select @@rowcount
GO
from the above procedure
it is inserting into two tables.Just i need if one table gets wrong value( for example in "t" table iam inserting integer value 5) both the table should be rollback.How to do this??????
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 11/29/2006 : 05:09:44
|
I assume you mean if there is an error in the t table (the 2nd script) and you want to rollback the whole thing?
Which then you will need to group both insert into 1 transaction, and check @@error for each insert, if its not 0 then rollback :)
|
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/29/2006 : 05:14:18
|
CREATE procedure hrd_emp
as
set nocount on
begin tran
insert into emp(idno,name) values('first','se')
if @error = 0
begin
insert into t(name) values('first')
if @error = 0
rollback tran
else
commit tran
end
else
begin
rollback tran
select 0
end
GO
You have to remember that @@ERROR is highly volatile. After querying the value, it becomes zero.
Peter Larsson
Helsingborg, Sweden |
|
|
|
sent_sara
Constraint Violating Yak Guru
India
328 Posts |
Posted - 11/29/2006 : 05:57:33
|
txs for ur reply,
CREATE procedure hrd_emp
as
begin
insert into emp(idno,name) values('first','se')
if @@error <>0
rollback tran
insert into t(name) values('aa')
if @@error <>0
rollback tran
end
GO
first 'name' column is a integer datatype in "t" table when i execute the procedure emp table gets inserted and in second insert it shows the error mentioned below:
Server: Msg 245, Level 16, State 1, Procedure hrd_emp, Line 7
Syntax error converting the varchar value 'aa' to a column of data type int.
as per me if one table gets error previous table(inserted table emp) should also get rollback..Awaiting for ur reply...
quote:
Originally posted by Westley
I assume you mean if there is an error in the t table (the 2nd script) and you want to rollback the whole thing?
Which then you will need to group both insert into 1 transaction, and check @@error for each insert, if its not 0 then rollback :)
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/29/2006 : 06:07:14
|
What is that? There is not even a BEGIN TRAN?
And not isolated ROLLBACKS?
Try my suggestion.
Peter Larsson
Helsingborg, Sweden |
|
|
|
sent_sara
Constraint Violating Yak Guru
India
328 Posts |
Posted - 11/29/2006 : 06:21:52
|
hai peso txs for ur reply its working fine...another query this is ok for 2 table.Incase i want to insert in more than 10 tables,whether i need to check by @@error for 10 statement(insert statement) or any other way????
quote:
Originally posted by Peso
CREATE procedure hrd_emp
as
set nocount on
begin tran
insert into emp(idno,name) values('first','se')
if @error = 0
begin
insert into t(name) values('first')
if @error = 0
rollback tran
else
commit tran
end
else
begin
rollback tran
select 0
end
GO
You have to remember that @@ERROR is highly volatile. After querying the value, it becomes zero.
Peter Larsson
Helsingborg, Sweden
|
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 11/29/2006 : 06:28:05
|
You need to add check for each statement...unless of course you use SQL Server 2005 which provides structured error handling with Try...Catch construct.
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
|
| |
Topic |
|
stored procedure
