Wednesday, March 26, 2008

Returning Complex Data from User-Defined Functions

Returning multiple values from a UDF can be done fairly easily in SQL Server, but we must return those values as a virtual table. The following very simple ParseEmail() function accepts an email address as an argument, parses it, and returns the username and domain separately:




create function EmailParse (@email varchar(1000))
returns @t table (UserName varchar(20), Domain varchar(20))
as
begin
declare @i int

select @i = charindex('@', @email,1);

if (@i > 1)
insert into @t values (left(@email,@i-1), substring(@email,@i+1,20))
else
insert into @t values (null,null)

return
end


how to use

select username, domain
from EmailParse('test@test.com')

No comments: