Calling SQL Server stored procedure with result set and return value

Question

Is there a way to get a dataset and a return value from a SQL Server stored procedure with just one execution of the stored procedure?

Is there a way to get both in just one call?

TIA Marcos Galvani

SqlConnection conn = new SqlConnection(ConnectionString);

// Pick the stored procedure to be executed
SqlCommand cmd = new SqlCommand("CustomersList02", conn);
cmd.CommandType = CommandType.StoredProcedure;

// Set the parameters and return value
cmd.Parameters.Add(new SqlParameter("@Email", email));
cmd.Parameters.Add(new SqlParameter("@ReturnVal", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue;

// Open the connection
conn.Open();

If I do this:

var dt = new DataTable();
dt.Load(cmd.ExecuteReader());

I don't get the return value, but if I do this:

cmd.ExecuteNonQuery();

I don't get the result set.

After doing `cmd.ExecuteReader()` did you check the value of Return value parameter? — Chetan, Dec 27 '22 at 13:52
For what it's worth, this way of accessing databases is very old. You can use Dapper or even Entity Framework to easily execute stored procedures and get a return value, in a single call and with many fewer lines of code. — Robert Harvey, Dec 27 '22 at 14:14
What does the stored procedure actually return? (If it's returning _data_ and not an error/status code, then I concur [it should be an output parameter instead](https://sqlblog.org/2009/10/09/bad-habits-to-kick-using-select-or-return-instead-of-output).) — Aaron Bertrand, Dec 27 '22 at 14:18

score 1 · Accepted Answer · answered Dec 27 '22 at 13:56

1

You can do this in a single call.

using (SqlDataReader reader = cmd.ExecuteReader())
{
    var dt = new DataTable();
    dt.Load(reader);
}

// getthe return value.
int returnValue = (int)cmd.Parameters["@ReturnVal"].Value;

answered Dec 27 '22 at 13:56

Vivek Nuna

25,472
25
109
197

Vivek, your code does work. but why it does not work in my code? the using is make a difference? – MarcosGalvani Dec 27 '22 at 14:05
1

because in one way you were not retrieving the return value and in another way `ExecuteNonQuery` returns the number of affected rows – Vivek Nuna Dec 27 '22 at 14:16

Calling SQL Server stored procedure with result set and return value

1 Answers1