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')