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

Working with ssms I see a "Return Value" and "Results" fields.  I want to get both in my c# code behind.  I can get either one with a SELECT at the end of my sproc, but have not found how to get both??

Thanks
Sam
SamCashAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't need to return the error since SQL Server engine will throw it for you to your code. You just need to have an error handle block in your code to process the error (try / catch (SqlException ex)).
0
 
käµfm³d 👽Commented:
In the proc
SELECT "Return Value", "Results" FROM [tableName]

Open 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);
        }
    }
}

Open 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.
0
 
SamCashAuthor Commented:
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
0
 
Ramachandar NCommented:
I understand your question. You are returning 2 things from your stored proc. 1 is the error code(@@ERROR) and the result.

This is usually returned as 2 different results(DataTables) to the C# DataSet. You need to access 2 DataTables to access the results.

DataTable errorcodetable = dataSet.Tables[0]; --Assuming you are first selecting(Select 1) the @@ERROR in your proc
DataTable resultdata = dataSet.Tables[1]; -- Select 2

Open in new window


int errorcode = errorcodetable.rows[0][0] --Since the DataTable that receives the @ERROR code contains only one row and a column, you can access it directly like this.
0
 
SamCashAuthor Commented:
Thanks much.  Sorry I have been on another assignment, just got back to this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.