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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SamCashAuthor Commented:
Thanks much.  Sorry I have been on another assignment, just got back to this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.