User defined SQL functions - Revision history

Psap: /* Miscellaneous */

2007-07-05T07:18:42Z

‎Miscellaneous

Psap: /* Miscelaneous */

2007-06-28T21:49:02Z

‎Miscelaneous

Psap at 21:47, 28 June 2007

2007-06-28T21:47:18Z

New page

==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

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

[[Category:ASE]]

← Older revision		Revision as of 07:18, 5 July 2007
Line 64:		Line 64:
	The basic definition of user defined SQL function is stored in sysobjects. The value of column type will be "SF".		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.
	[[Category:ASE]]		[[Category:ASE]]

@@ Line 61: / Line 61: @@
   drop function get_ip_part
-==Miscelaneous==
+==Miscellaneous==
 The basic definition of user defined SQL function is stored in sysobjects. The value of column type will be "SF".
 [[Category:ASE]]