How Can I Trap Errors In A Stored Procedure?

My simple question:





Is there any way to prevent unimportant errors in a stored procedure from causing exceptions in my C# code? This is preventing the SqlAdapter from filling the query results into my DataSet.





The Setup:





I have a Stored Procedure in Sql Server 2000 which has a text parameter called @Xml. I send in an Xml document to process. This document contains several "records" to process. The format of the xml really isn't important.





I create a temporary table called #Results to hold the results of processing each record in the xml.





To process the xml I have a Cursor which loops over a SELECT from the xml.





For each record, the sproc attempts to make a series of INSERTs and UPDATEs inside of a transaction. Any one of these commands may fail because of constraint violations or attempts to insert NULL into non-null columns, or such. After each command I check @@ERROR. If it is not zero, I stop processing the record and rollback the transaction. The cursor loops around and tries the next record. Each time the success or failure of the transaction is recorded into the #Results table.





When the cursor is done looping I 'SELECT * FROM #Results'.





I've tested this many times in the Query Analyzer and each time, regardless of any errors, I can see the result set from the SELECT of the #Results table in the Grids tab. The Messages tab shows each of the errors that occurred.





I try to call this stored procedure using the following code:








int c = 0;





try


{


sqlAdapter.Fill( sqlDS );


}


catch( System.Data.SqlClient.SqlException )


{


c = sqlDS.Tables.Count;


}





The value of c will always be zero, if there were any errors during the execution of the stored procedure. The DataSet does not get filled, even though the stored procedure is returning a result set. This is a problem for me because I expect errors to occur, and I need to know which records from the Xml caused those errors.





Is there any way to clear the errors in my stored procedure so that they don't turn into exceptions in my code? Or, is there anyway to get the Adapter to fill the DataSet regardless of any errors that were encountered?





I've also tried this with a SqlDataReader. The reader never gets assigned to because SqlCommand.ExecuteReader() throws an exception.