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
 Site Related Forums
 Article Discussion
 Article: How to NOT trap errors in a stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/02/2001 :  17:38:15  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
I was trying to track down an error in one of our internal OLAP collection processes, when I noticed something very peculiar: An insert was failing, appeared to have the proper error trapping, but was not causing an error. How does that work?

Article Link.

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/02/2001 :  23:02:30  Show Profile  Visit nr's Homepage  Reply with Quote
insert into BigTable
select * from ImportTable

set @RC = @@ROWCOUNT
set @err = @@ERROR

>> appeared to have the proper error trapping.
Where is that?
It will trap any errors in set @RC = @@ROWCOUNT

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 12/02/2001 :  23:58:15  Show Profile  Visit Merkin's Homepage  Reply with Quote
I think that is the point Nigel

Damian
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/03/2001 :  05:20:00  Show Profile  Visit nr's Homepage  Reply with Quote
That was meant to be irony - something we have in the UK.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 12/03/2001 :  08:05:40  Show Profile  Visit Merkin's Homepage  Reply with Quote
I was being sarcastic.
Something we have in Australia



Damian
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/03/2001 :  08:59:30  Show Profile  Visit nr's Homepage  Reply with Quote
Makes up for the lack of proper beer.
First to get a berr regference in .

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

elmes
Starting Member

1 Posts

Posted - 12/11/2001 :  03:51:35  Show Profile  Reply with Quote
There's another bug with this example. Using

select @RC = @@ROWCOUNT, @err = @@ERROR

in this case still won't work as the test being done is:

if @@ERROR <> 0
begin
rollback transaction
print 'Transaction Failed'
end


You need to test @err not @@ERROR. Otherwise your checking for the sucess of the select statement.




Go to Top of Page

tinks
Starting Member

United Kingdom
34 Posts

Posted - 12/11/2001 :  07:13:19  Show Profile  Visit tinks's Homepage  Send tinks an ICQ Message  Reply with Quote
Wouldnt the @@ERROR test the select @RC = @@Rowcount?

To the best of my knowledge the @@ERROR would be testing the last executed SQL statement. AFAIK the @@vars test the last statement so you have to add them directly after the statement you want to test.

Of course I may be wrong!



Taryn-Vee
@>-'-,---
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 12/11/2001 :  09:13:53  Show Profile  Visit nr's Homepage  Reply with Quote
You're not wrong.
As elmes says
"You need to test @err not @@ERROR. Otherwise your checking for the sucess of the select statement."


==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 12/13/2001 :  14:30:49  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Congratulations Chris (RocketScientist) and SQLTeam! This short article stirred enough interest to be linked to from SQLWire.com (http://www.sqlwire.com/brief.asp?1783) More exposure is a good thing!

-------------------
It's a SQL thing...
Go to Top of Page

REDDY
Starting Member

43 Posts

Posted - 09/26/2002 :  14:04:36  Show Profile  Reply with Quote
I ALSO GOT THE SAME PROBLEM BUT SOLVED USING THE BELLOW GIVEN STATEMENT.

SET XACT_ABORT OFF


Go to Top of Page

Dommi
Starting Member

2 Posts

Posted - 10/15/2002 :  09:26:33  Show Profile  Reply with Quote
Is there any way to handle a fatal error?

Because I am trying to make an insert, but the inserted Data is not the datatype the table expects. It's easy to handle this error by changing the datatype of the column but I am trying to create this Table automatically...
and I can not be sure that the data I am trying to insert is always the same and always in the proper format.

If anyone knows an answer please let me know

Dommi

Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 10/15/2002 :  09:38:25  Show Profile  Visit robvolk's Homepage  Reply with Quote
You can't "handle" a fatal error...just like you can't cure or heal a fatal injury. The only thing you can do is to take steps to prevent it happening (which is the best kind of error handling there is!) Regardless, it would be better for you to work at improving the quality of the data file you're importing. Get whoever is supplying it to you to clean it up.

Failing that, import the file into a generically formatted staging table. This table has no constraints, allow nulls, etc., and stores the data as varchar. Then you can apply CAST and CONVERT functions to change the data over to its proper data type and then INSERT it into the final destination table. This is a fairly common technique and is much faster and more reliable than trying to construct the "right" table on the fly.

Edited by - robvolk on 10/15/2002 09:39:55
Go to Top of Page

Dommi
Starting Member

2 Posts

Posted - 10/15/2002 :  09:59:42  Show Profile  Reply with Quote
I am not able to get better data, it is not in my responsibility. But maybe it is not a fatal-error if i try an Alter-Table statement with bad data...

So lets try and learn

Dommi

btw. Thanks for your quick reply

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.11 seconds. Powered By: Snitz Forums 2000