Friday, March 9, 2012

Reasonable to assemble FullName W/ A UDF

I break somone's name in a table in fields as First, Last, Middle,
Suffix.

Is it reasonable to use a UDF to create the full name as a string if I
need it, by passing it the First, Last, Middle, & Suffix fields?

Is this really slow to do? It's just sometimes I want to do a GroupBy
Query and I want to include the person's name in the Select of the
query and I don't know if the group by would work if I returned each
column seperately.

Any opinions on this?

Thanks.Using the function might be useful for consistency. I have worked in
databases where we have identical code all over the place: e.g.
LastName + ', ' + FirstName + ' ' + MiddleName.

I function would be nice for that. As far as speed goes, It may have a
negative effect.

Another option is to create a view of your data that has a FullName
column based on the others.|||Actually a computed columns works really nice and is probably easier to
use than a UDF. That way you can change the logic anytime you want and
don't have to change any application code.

if(not exists(
select syscolumns.name from sysobjects INNER JOIN syscolumns ON
sysobjects.id = syscolumns.id
where sysobjects.name = 'Users' AND syscolumns.name = 'DisplayName'
))
begin
alter table Users add DisplayName as FirstName + ' ' + LastName + ' ('
+ Company + ')'
end

if(not exists(
select syscolumns.name from sysobjects INNER JOIN syscolumns ON
sysobjects.id = syscolumns.id
where sysobjects.name = 'Users' AND syscolumns.name =
'SimpleDisplayName'
))
begin
alter table Users add SimpleDisplayName as FirstName + ' ' + LastName
end

if(not exists(
select syscolumns.name from sysobjects INNER JOIN syscolumns ON
sysobjects.id = syscolumns.id
where sysobjects.name = 'Users' AND syscolumns.name =
'ReverseSimpleDisplayName'
))
begin
alter table Users add ReverseSimpleDisplayName as LastName + ', ' +
FirstName
end

No comments:

Post a Comment