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.what exactly is the error message you're getting back? I mean... what's the exception. What if it's not the @.@.error returning, and it's something else wrong?|||catch( System.Data.SqlClient.SqlException )

that shouldn't even compile.|||The errors I get back are errors I expect. Like a #515(?): "Cannot insert Null into column Foo, it does not allow nulls."

The records that don't have errors get inserted just fine. I just can't get my result set back.|||> catch( System.Data.SqlClient.SqlException )

> that shouldn't even compile.

Thanks for taking a look at my problem, I appreciate it, but you should look up the syntax of the catch statement. There are three variations:

catch

catch( Exception )

catch( Exception exc )

Why exactly shouldn't it compile?|||I found some disheartening info at the following url:

http://www.sommarskog.se/error-handling-I.html

SqlClient:

"If the procedure produces an error before the first result set, you cannot access any data with any of the methods. (ExecuteReader does not even return a SqlDataReader object.) If you need to access data in this case, Odbc is your sole possibility. "

Odbc:

"Still, there is one situation where Odbc is your sole choice, and that is if you call a stored procedure that first produces an error message and then a result set. The other two providers never return any data in this situation. With Odbc you can do it – but it is a narrow path to follow. You must have SET NOCOUNT ON. If you only have one result set, you can probably use OdbcDataAdapter.Fill. If there are more than one result set, you must use ExecuteReader, and you must specify the CommandBehavior SingleResult (!). You may get an exception about Function Sequence Error at the end, but by then you have retrieved all your data."

Tried it, didn't work.

I thought of a work-around but I'm not sure I want to do it. Basically, if I store the Results in a permanent table, I can make a separate request for that data after the first sproc returns. The only problem is it would be tricky to match up the input records to the records in the Results table. Since I only have a phone number as a pseudo-unique identifier, but it can repeat. I would probably have to insert blank Results first, add those ID's to my xml, then update those Result records as I process each record.|||Really, I didn't know you can do catch ( Exception )

You learn something new every day.|||>Really, I didn't know you can do catch ( Exception )

Yes, it's handy when the code you are putting in your catch block is insignificant except to clean up resources. You can rethrow the exception when you are done with this simple syntax:

catch( Exception )
{
//clean up resources
throw;
}