User Defined Function (UDF)

[Former Member]

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