Error handling in sql server

Hi,
I have a job looping through a table containing names of databases. Based
on some criterias I want to detach some of the databases.
So far all is good.
If for some reason the detach fails, the job fails and quits.
I would like to know if I in any way can get the job to continue the loop
without aborting the job?
Today the log can look like:
Detached database p2419983 [SQLSTATE 01000] - detached ok
Msg 15010, Sev 16: The database 'p2489947' does not exist. Use sp_helpdb
to show available databases. [SQLSTATE 42000] - failed to detach
I am aware that I should solve the real problem here (database does not
exist), but I would like to know anyway...
Any help will be appreciated.
Thanks,Hi Gurba,
Some really good article about that can be found here:
http://www.sommarskog.se/error-handling-I.html
HTH, jens Suessmeyer.|||If a function does not exist, then the db_id() function will return NULL.
For example:
if db_id(@.dbname) is not null
begin
. . .
. . .
end
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns97097A4DAA9F6gurbaohotmailcom@.12
9.250.171.65...
> Hi,
> I have a job looping through a table containing names of databases. Based
> on some criterias I want to detach some of the databases.
> So far all is good.
> If for some reason the detach fails, the job fails and quits.
> I would like to know if I in any way can get the job to continue the loop
> without aborting the job?
> Today the log can look like:
> Detached database p2419983 [SQLSTATE 01000] - detached ok
> Msg 15010, Sev 16: The database 'p2489947' does not exist. Use sp_helpdb
> to show available databases. [SQLSTATE 42000] - failed to detach
> I am aware that I should solve the real problem here (database does not
> exist), but I would like to know anyway...
> Any help will be appreciated.
> Thanks,

Error Handling in a stored procedure

I have an application where I am bcping a file into a holder table and then looping through each of the rows in that table to add it into the main table for the app. If the data is improperly formatted (ie someone accidently enters 39.Y6 instead of 39.66), we still want to keep it in the system, so we can update the bad fields manually and then import it into the system and keep going through the loop so that if there are 50 rows to import and only one of them is bad, the other 49 will still get imported fine.

I am putting each field from the holder table into a variable and passing them into an existing stored procedure that updates the main table. I had originally checked each one of those variables that had to be numeric or a date to make sure that it was correct before passing them into the procedure but there are about 30 fields so this made the application run unacceptably slow. I then just checked the @.@.Error value after passing them into the stored procedure (tried after the sp and after the INSERT statement inside the sp) to get that this row didn't insert correctly and move onto the next one. The problem is that the "Error converting data type varchar to numeric" doesn't seem to be handled by the error handling and just bombs the whole thing, so none of the subsequent rows or processing is done.

Is there any way to handle this error and continue the processing without the whole stored procedure crashing? The data entry is being outsourced to India (grrr...), so I don't have any control over checking the data when they enter it and have to do it from within my application on the database side.What you can probably do is in your insert stored procedure, set up an output parameter to send any error code back to the calling stored procedure. The error in your insert probably won't bubble up so do something like this

Create Procedure InsertIt
(
@.fields ...,
@.errorcode int = null output
) as
insert into...
set @.errorcode = @.@.error
return

the error will be returned back as an output parameter rather than you trying to use @.@.error.

I've done similar things in the past and what I did was run a query that searched for the possible error conditions and flagged those records as needing attention. Then just do an insert ... select query, selecting only unflagged records, into your main table. As long as you make sure data types are good to go, you shouldn't have a failure. Probably faster than using a cursor and checking each field with in each iteration. Of course, if you the insert is more complex than just a plain import, the cursor may be necessary. Just a thought.