Difference between revisions of "User defined SQL functions"

Latest revision as of 08:18, 5 July 2007

Introduction

User defined SQL functions are a new feature in ASE 15.0.2. This document shows you how to work with them.

This example demonstrates how to return a part of a string filled with an ip-address. The returned value should of the datatype tinyint. So for ip-address 192.138.151.104 and part 3 the returned value should be 151.

Create the function

Goes like this:

create function get_ip_part
       (@ip_address varchar(30), @part tinyint)
       returns tinyint
as

if     @part not between 1 and 4
begin
       raiserror 20000 "Invalid value for @part, must be between 1 and 4"
       return
end

declare @j tinyint,
        @i tinyint

select  @j = 1,
        @i = charindex(".",@ip_address)

while   @i > 0
begin
        if @j = @part
           return convert(tinyint,left(@ip_address,@i - 1))

        select @ip_address = substring(@ip_address,@i+1,255),
               @j          = @j + 1
        select @i          = charindex(".",@ip_address)
end

return  convert(tinyint,@ip_address)

Execute the function

You can execute the function in the same manner as a Sybase supplied function:

select get_ip_part("192.138.151.104",3)

However, in the pre-release of 15.0.2 you need to specify dbo in front of the name:

select dbo.get_ip_part("192.138.151.104",3)

When you do not do so, you will get this error:

Msg 2847, Level 18, State 1:
Server 'ASE1', Line 6:
The object 'dbo.get_ip_part' is not a SQLJ function. If this is a SQL function, specify owner.objectname.
Msg 14216, Level 16, State 1:
Server 'ASE1', Line 6:
Function 'get_ip_part' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists
(sp_help may produce a large amount of output).

Cross database execution is possible, as in my_database.dbo.get_ip_part

Drop a function

Can be done with the drop statement:

drop function get_ip_part

Miscellaneous

The basic definition of user defined SQL function is stored in sysobjects. The value of column type will be "SF".

Internally, within ASE, user defined SQL functions behave the same as stored procedures. This means that there is a query plan for them and you can access tables in user defined SQL functions. Since you can make these functions part of a where clause in a select statement, performance can really degrate since for each evaluation a stored procedure is executed.