Although this first section is already covered by Rob Verschoor (www.sypron.nl) i would like to repeat a part of his description because it makes ASE life a lot easier.

ASE 15.x supports ‘scalar-valued SQL UDFs’, which are UDFs returning a single value only. This is to distinguish from ‘table-valued SQL UDFs’, which return a table: ASE does not support such UDFs at this point.

SQL UDFs are located in the database where they are created, but they can be called from other databases.

A SQL UDF must be prefixed with the owner name of the function. This follows the same convention as used by SQL UDFs in MS SQL Server.

An important practical aspect of SQL UDFs is that you can name them with an “sp_” prefix, and place them in the sybsystemprocs database so that the UDF will be accessible from anywhere in the ASE server. Without this trick, you’d have to explicitly specify the database where the UDF is located.

Note! You cannot use this trick with UDFs in MS SQL Server!

Example 1:

We want to know in which directory a certain datadevice for further use in a stored procedure. We can retrieve this from sysdevices but we are only interested in the directory not the full path. By making use of a UDF we can achief this. This function can be applied to every query that returns a full path.

The basic query:

1> select phyname from master..sysdevices where vdevno = 1

2> go

phyname

——————————————————————

C:\Sybase\data\sysprocs.dat

(1 row affected)

Now with a the getDir function:

1> declare @test varchar(100)

2> declare @output varchar(100)

3> select @test = phyname from master..sysdevices where vdevno = 1

4> select @output = dbo.getDir(@test)

5> print @output

6> go

(1 row affected)

(1 row affected)

C:\Sybase\data\

1>

Code: getDir function

-- remove the function if exists
if(object_id("getDir")) != 0
begin
   drop function getDir
end
go
------------------------------------------------------------
-- File: action_getDir.sql  
-- Name: getDir
-- Desc: Get the directory from a path without the file
-- Auth: H. Splinter
-- Date: 08-02-2014
------------------------------------------------------------
-- Change History
------------------------------------------------------------
-- PR   Date     Author         Description
------------------------------------------------------------
-- 001  09-02-2014   H. Splinter    Remove getDir if exists
------------------------------------------------------------
-- create the function
create function getDir(@path varchar(1000))
returns varchar(1000)
as
begin
  -- declarations
  declare @dir varchar(100)
  -- extract the directory
  select @dir = reverse(substring(reverse(@path),(charindex("\",(reverse(@path)))),((char_length(@path)+1)-(charindex("\",(reverse(@path)))))))
  -- return the value
  return @dir
end

Nested User Defined Function

Although undocumented a UDF can call another UDF.

In the next example we determine the directory separator based on the Operating System(OS) where the ASE server is installed. This can be used as a “dynamic” variable in per example the build of a dump location string.

1> select dbo.dirSeperator(dbo.os())

2> go

\

(1 row affected)

1>

Code: os function

-- remove the function if exists
if(object_id("os")) != 0
begin
   drop function os
end
go
------------------------------------------------------------
-- File: action_os.sql
-- Name: os
-- Desc: Returns the operating system ASE is on as an INT.
--       (AIX (0) | HP-UX (1) | Linux (2) | Sun (3) |  VMS (4) | Windows (5))
-- Auth: H. Splinter
-- Date: 26-11-2012
------------------------------------------------------------
-- Change History
------------------------------------------------------------
-- PR   Date     Author         Description
------------------------------------------------------------
-- 001  28-12-2012   H. Splinter  Remove function if exists
-- 002  20-02-2014   H. Splinter  Remove clutter
------------------------------------------------------------
-- create the function
create function os
returns int
as
begin
  -- declarations
  declare @os int
  -- get the OS and set the return variable
  if (select patindex('%AIX%', @@version))<> 0
  set @os = 0
  else
  if (select patindex('%HP-UX%', @@version))<> 0
  set @os = 1
  else
  if (select patindex('%Linux%', @@version))<> 0
  set @os = 2
  else
  if (select patindex('%SUN%', @@version))<> 0
  set @os = 3
  else
  if (select patindex('%VMS%', @@version))<> 0
  set @os = 4
  else
  if (select patindex('%Windows%', @@version))<> 0
  set @os = 5
  return @os -- return the corresponding OS number.
end

Code: dirSeperator function

-- remove the function if exists
if(object_id("dirSeperator")) != 0
begin
   drop function dirSeperator
end
go
------------------------------------------------------------
-- File: action_dirSeperator
-- Name: dirSeperator
-- Desc: Returns the right directory seperator for tje operating system ASE is on as an char.
--       (AIX (/) | HP-UX (/) | Linux (/) | Sun (/) |  VMS (/) | Windows (\))
-- Auth: H. Splinter
-- Date: 26-11-2012
------------------------------------------------------------
-- Change History
------------------------------------------------------------
-- PR   Date     Author         Description
------------------------------------------------------------
--
------------------------------------------------------------
-- create the function
create function dirSeperator(@os int)
returns char(1)
as
begin
  -- declarations
  declare @seperator char(1)
  -- set the return variable
  if (select @os)= 5
     set @seperator = "\"
  else
     set @seperator = "/"
  return @seperator -- return the corresponding seperator
end

Although the above examples are simple. They can be very powerfull in terms of repeating the same job over and over again. They are small, quick and handy! I am building every small job into a function when possible and, as shown above, get code I can reuse on basicly every OS/Platform with ASE.

Aknowledgements:

User Defined Functions – Rob Verschoor www.sypron.nl