Link to home
Create AccountLog in
Avatar of SamCash
SamCashFlag for United States of America

asked on

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
Avatar of kaufmed
kaufmed
Flag of United States of America image

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.
Avatar of SamCash

ASKER

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
SOLUTION
Avatar of Ramachandar N
Ramachandar N

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of SamCash

ASKER

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