How to retrieve stored procedure return values from a TableAdapter

Question

I cannot find an elegant way to get the return value from a stored procedure when using TableAdapters.

It appears the TableAdapter does not support SQL stored procedure return values when using a non-scalar stored procedure call. You'd expect the return value from the auto-generated function would be the return value from the stored procedure but it isn't (it is actually the number of rows affected). Although possible to use 'out' parameters and pass a variable as a ref to the auto generated functions it isn't a very clean solution.

I have seen some ugly hacks on the web to solve this, but no decent solution. Any help would be appreciated.

Answer 1

The way to get the return value is to use a SqlParameter on the SqlCommand object which has its Direction set to ParameterDirection.ReturnValue. You should check the SelectCommand property of the TableAdapter after calling Fill.

Answer 2

I cannot say for certain because I have do not use TableAdapters, but you might need to look at your stored procedure and include the following around your procedure.

SET ROWCOUNT OFF

BEGIN
<Procedure Content>
END

SET ROWCOUNT ON

Answer 3

NOTE: The way to go is using a SqlParameter where the Direction = ParameterDirection.ReturnValue

With that said, as someone already mentioned SqlParameters, here is a dynamic method alternate using a DataSet. (if thats how you ride):

Example SQL statement and C# as fallows:

string sql = @"DECLARE @ret int 
            EXEC @ret = SP_DoStuff 'parm1', 'parm2'
            SELECT @ret as ret";

DataSet ds = GetDatasetFromSQL(sql); //your sql to dataset code here...

int resultCode = -1;
int.TryParse(ds.Tables[ds.Tables.Count-1].Rows[0][0].ToString(), out resultCode);

The stored procedure results are loaded into a DataSet and will have as many DataTables as return select statements in the stored procedure.

The last DataTable in the DataSet will have 1 row and 1 column that will contain the stored procedure return value.

Answer 4

Closing this question as it appears return values aren't supported and there is no elegant workaround!

Answer 5

http://blogs.msdn.com/smartclientdata/archive/2006/08/09/693113.aspx