There is not much in the way of being able to trap errors in SQL. @@error will have the error number and you can use RAISERROR to pass a specific message if you wish. Looks at both of these in BOL, or if need more specific input, describe what you need to do and in what type of process.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)