SQL C# Get return value (ok or error) and the result of query

In the proc

SELECT "Return Value", "Results" FROM [tableName]

Select allOpen in new window

In the code

using (SqlConnection con = new SqlConnection("your connection string"))
{
    using (SqlCommand cmd = new SqlCommand("your proc name", con))
    {
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;

        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read)
        {
            object returnValue = reader.GetValue(0);
            object results = reader.GetValue(1);
        }
    }
}

Select allOpen in new window

I've used object as the type for the two variables, but that's only because you haven't specified what the data types of those two columns are. Change the types to match whatever the data types of your columns are.

Kaufmed,

Thanks much.

In my effort to be brief I lost my question.

I am trying to get both the sql error code (@@ERROR) and my returned DataSet, Object, etc.

I want to check sql errors before processing the returned data, or handle the errors.  I like Try/Catch for this stuff.  I do not know how to get the sql errors into my c#.

I believe this has been solved before and I am not quite using the correct search terms.  I want to use best practices and not reinvent (a poor solution), I am new to sql.

Hopefully a better example of my question (including your suggestions)

BEGIN
WITH myData AS (

SELECT ID, First, Middle, Last FROM TableOfNames
                               )

SELECT @@ERROR, myData
END

c#

int SqlError = reader.GetValue(0);
object obj = reader.GetValue(1);

Thanks again, I hope this clarifies my question.
Sam

Thanks much.  Sorry I have been on another assignment, just got back to this.