stored procedure - 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

SQL Server Development (2000)

stored procedure

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2006-11-29 : 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 - 2006-11-29 : 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

30421 Posts

Posted - 2006-11-29 : 05:14:18

[code]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[/code]
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

377 Posts

Posted - 2006-11-29 : 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

30421 Posts

Posted - 2006-11-29 : 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

377 Posts

Posted - 2006-11-29 : 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
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-29 : 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

22864 Posts

Posted - 2006-11-29 : 09:40:56

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources