Difference between revisions of "User defined SQL functions"

Revision as of 22:49, 28 June 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".