User Functions en MySQL

Ejemplo de User Function en MySQL, con el uso de cursores.

DROP FUNCTION IF EXISTS importacion.set_tiponodo_adsl;

delimiter '/';
CREATE FUNCTION importacion.set_tiponodo_adsl() returns varchar(20)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE val_edificio varchar(20);
DECLARE val_equipo varchar(20);
DECLARE cablepriaux VARCHAR(20);
DECLARE tiponodoaux VARCHAR(20);
DECLARE val_rangonodos VARCHAR(20);
DECLARE cur1 CURSOR FOR SELECT edificio, equipo FROM importacion.adslaux WHERE tiponodo = 'NO' OR tiponodo IS NULL;

OPEN cur1;
IF (Select FOUND_ROWS()>0) THEN
dependency_loop: LOOP
FETCH cur1 INTO val_edificio, val_equipo;
IF NOT done THEN
SET tiponodoaux = '';

SELECT tiponodo INTO val_rangonodos
FROM importacion.rangonodos
WHERE edificio = val_edificio
AND lim_inferior <>
AND lim_superior > cast(val_equipo as UNSIGNED );

IF val_rangonodos IS NULL THEN
SET tiponodoaux = 'NO';
ELSE
SET tiponodoaux = val_rangonodos;
END IF;

UPDATE importacion.adslaux
SET tiponodo = tiponodoaux
WHERE edificio = val_edificio
AND equipo = val_equipo;

ELSE
LEAVE dependency_loop;
END IF;
END LOOP dependency_loop;
END IF;
CLOSE cur1;

RETURN '';
END/
delimiter ';'/

Para invocarla simplemente hacer
SELECT importacion.set_tiponodo_adsl;

Ejemplo de User Defined Function

Ejemplo de User Function en SQL Server 2005

USE [name_db]
GO
/****** Object: UserDefinedFunction [dbo].[getAccounts] Script Date: 03/31/2009 15:31:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[getAccounts]
(@kchecks varchar(10))
RETURNS varchar(max)
AS
BEGIN
declare @Return varchar(max)
DECLARE @temp table (
[tmp_field] [varchar] (max)
)

set @Return = ''

INSERT INTO @temp SELECT Account.account_id
from advocate_mig_final.dbo.checks
inner join advocate_mig_final.dbo.chckjrnl on chckjrnl.kchecks = checks.kchecks
inner join advocate_mig_final.dbo.journal on journal.kjournal = chckjrnl.kjournal
inner join advocate_db_final.dbo.Account on Account.leg_kaccounts = journal.kaccounts
where journal.djournal > '12/31/1999' and checks.kvictims != '111111'
and checks.kchecks =@kchecks


select @Return = @Return +','+ tmp_field
from @temp

If len(@Return) > 0 Select @Return = right(@Return, len(@Return) - 1)


return @return
end


--select advocate_mig_final.dbo.getAccounts('AFCAYP')