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();

asked 10 May '12, 18:59

Jturner's gravatar image

Jturner
1816714
accept rate: 66%

edited 14 May '12, 09:07

Calvin%20Allen's gravatar image

Calvin Allen
1.5k232638


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

permanent link

answered 11 May '12, 03:52

Volker%20Barth's gravatar image

Volker Barth
30.0k294447654
accept rate: 32%

edited 11 May '12, 03:54

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.

(11 May '12, 11:33) Jturner
Replies hidden

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

(11 May '12, 11:37) Volker Barth

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.

(14 May '12, 03:56) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×409
×35

question asked: 10 May '12, 18:59

question was seen: 2,002 times

last updated: 14 May '12, 09:07