|
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(); |
|
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
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 and second result set 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
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
|
