User defined SQL functions
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 220.127.116.11 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:
However, in the pre-release of 15.0.2 you need to specify dbo in front of the name:
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
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.