SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 11/29/2006 :  05:04:39  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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 :)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 11/29/2006 :  05:14:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 11/29/2006 :  05:57:33  Show Profile  Reply with Quote
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 :)


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 11/29/2006 :  06:07:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What is that? There is not even a BEGIN TRAN?
And not isolated ROLLBACKS?

Try my suggestion.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 11/29/2006 :  06:21:52  Show Profile  Reply with Quote
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

Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 11/29/2006 :  06:28:05  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 11/29/2006 :  09:40:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000