How do I pass an array of values to a function in Sybase (15.x)

Question

It seems that Sybase omitted the function median() in Sybase ASE (15.x) while the typical, sum(), min(), max(), count(), etc... are available.

As a result, I was thinking that I could create a UDF (User Defined Function) that would fill that gap. I see a few examples of UDF taking a value (or fixed set of values) and returning a value; like this one: http://www.sypron.nl/udf.html.

Unfortunately, I don't see any example where a function takes an array of values as parameters. I saw the ugly hack to concatenate all the values into a long string and pass that, but I would rather try to explore a cleaner way of doing it. I could also require that whatever calls the function to insert the data into a predetermined tmp table that can then be read by the function, but that seems ugly too.

Any suggestions?

Answer 1

You can use temporary table to use it as array. Consider below example

create table #t
(
  id int
)

insert into #T values (1)    

create function fun
returns int
as
   declare @id int
   select @id = id from #T
   return @id
go

select dbo.fun()