cancel
Showing results for 
Search instead for 
Did you mean: 

ADO.NET Problem returning values for output and returnvalue parameters

Former Member
4,124

No value us returned to the parameters after the execute command. Has anyone done this?

Here is the store proc I used.

create or replace procedure jay(out @val int)
begin
select 25;
set @val = 34;
return 33;
end;

As you can see this does it all. Has an output parameter and a result set and a return value.

I can read the result set but I never have any value but 0 in the paramter set after the execute.

The following is the code I used to test this out.

var connString = ConfigurationManager.ConnectionStrings["dbConnect"];
var conn = new SAConnection(connString.ConnectionString);
conn.Open();
var cmd = new SACommand();

string cmdstring = "jay";
cmd.Connection = conn;

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SAParameter() { ParameterName = "@ret", Direction = ParameterDirection.ReturnValue, SADbType = SADbType.Integer });

cmd.Parameters.Add(new SAParameter() { ParameterName = "@val", Direction = ParameterDirection.Output, SADbType = SADbType.Integer });

cmd.CommandText = cmdstring;

var ret = cmd.ExecuteScalar();

conn.Close();

Accepted Solutions (0)

Answers (1)

Answers (1)

VolkerBarth
Contributor
0 Kudos

I don't find a doc reference for that, but I think I remember that when using interfaces like ODBC or ADO with a stored procedure that does return both a result set and output parameters and/or a return value, you can either

  • access the result set or
  • access the return value and output parameters,

but not both at the same time.

I guess the reason is that the result set is returned beforehand and has to be completely fetched before the other values are available, and that is usually not possible with these interfaces.

More on this can be found in Mark's answer on a related topic.


In contrast, ISQL does allow both kind of accesses - and that leads to 2 result sets, as in your sample:

begin
  declare @MyOutputVal int;
  declare @MyReturnVal int;
  @MyReturnVal = call jay(@MyOutputVal);
  select @MyOutputVal, @MyReturnVal;
end;

returns first result set
25
25

and second result set
@MyOutputVal,@MyReturnVal
34,33

Former Member
0 Kudos

I was not clear, your example code is in ISQL, I have no problem doing as you did there but when calling from a C# application I have the problem. The documentation for ASE ADO access shows and example of how to access parameters. By the way the ADO API controls how this should work, you must always read to the end of the cursor to see the parameter values. I have had no problem doing this in Microsoft SQL server but ASA 12 does not seem to implement this properly. Is there a trick to it is what I want to know.

VolkerBarth
Contributor
0 Kudos

Well, I was aware that you are asking about ADO...

It's just my impression/memory that this does not work as you expect (and it's different from MS SQL Server and possibly from ASE). - But as stated, that's just my humble impression, others may know better:)

VolkerBarth
Contributor
0 Kudos

Obviously, it's a question on ADO.NET, not ADO...

I don't have further hints, but I guess ADO.NET just lets you choose between

  • getting a result set (with SACommand.ExecuteScalar() or SACommand.ExecuteReader())
  • getting the return value and output parameters (with SACommand.ExecuteNonQuery()).

I hope a SQL Anywhere expert will confirm that - or will correct me, if there's a way to combine both return pathes in one call.


FWIW, in such cases where I would have preferred a result set plus a return value, I usually have modified my stored procedures by turning return values and output parameters into a (possibly second) result set, by this choosing just one "return path". If you use more than one result set, I guess SADataReader.NextResult() will move to the next result set.