Out parameters in functions

[Jim Diaz]

I would like them please.

Thanks

Jim

[Kory Hodgson (Sybase iAnywhere)]

Is there a reason that this would be better then creating a procedure
instead of a function which does have out parameters?

[Jim Diaz]

Maybe not, but here is the situation I currently have and can't figure out
how to solve without these.

I have a table with a long binary field which stores integer data. I also
have a soap web service which needs several rows of this data as well as min
and max values. To produce the results the service calls a procedure which
returns a result set. Within this procedure I have a select statement
similiar to

SELECT C1, C2, xyz.BinaryToJSON(BlobField) AS JSONVarchar
FROM abc
WHERE x, y, z

I would like this to be

DECLARE @MinValue INTEGER,
DECLARE @MaxValueINTEGER,

SELECT C1, C2, xyz.BinaryToJSON(BlobField) AS JSONVarchar, @MinValue,
@MaxValue
FROM abc
WHERE x, y, z

I tried to accomplish this using connection level variables but the values
were not valid, each row returns the values assigned to the last row in
result set. My thinking was an out or inout perameter to the function would
solve this.

Thanks

Jim

"Kory Hodgson (Sybase iAnywhere)" <khodgson@A_SPAM_FREE_sybase.com> wrote in
message news:4aeb4aa8$1@forums-1-dub...

[Glenn Paulley [Sybase iAnywhere]]

Jim, if I recall you're running 9.0.2, right?

If so, I don't believe there is anything preventing you from
re-configuring your function as a stored procedure that returns a result
set - in this case, seemingly a single-row result with 3 values:
minvalue, maxvalue, and "jsonvarchar" if I follow you correctly.

Then you can re-cast your outer-most SELECT to refer to the procedure as
a table function, ie

SELECT abc.x, abc.y, dt.minvalue, dt.maxvalue, dt.JSONValue, ...
FROM abc, LATERAL ( xyz.BinaryToJSON(BlobField) ) as dt
WHERE ...

Glenn

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport

[Jim Diaz]

Glenn,

You are brilliant!

Thanks,
Jim

"Glenn Paulley [Sybase iAnywhere]" <pau...@ianywhere.com> wrote in message
news:4aeb59a8$1@forums-1-dub...