×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using UDF In Select, Bad Idea?
2

Using UDF In Select, Bad Idea?

Using UDF In Select, Bad Idea?

(OP)
I have read that UDF's in selects are bad.
Are all UDF's bad or does it depend on the UDF?

In the past I have used, but thought a UDF might be better because I format pone numbers in several procs.
Or maybe I should go back to formatting in .NET app.

CODE

SELECT Substring(CM.Phone,1,3) + ''-'' + Substring(CM.Phone,4,3) + '-' + Substring(CM.Phone,7,4) + ' ' + Substring(CM.Phone,11,10) END As CustPhoneStr 

I also have a UDF

CODE

ALTER FUNCTION [dbo].[Udf_FormatPhoneNumber](@PhoneNbr VARCHAR(20))
RETURNS VARCHAR(23)
AS
BEGIN
DECLARE @PhoneFormatted VARCHAR(23)

IF (LEN(@PhoneNbr) < 10)
    SET @PhoneFormatted = @PhoneNbr
ELSE
    SET @PhoneFormatted = LEFT(@PhoneNbr, 3) + '-' + SUBSTRING(@PhoneNbr, 4, 3) + '-' + SUBSTRING(@PhoneNbr, 7, 4) + ' ' + SUBSTRING(@PhoneNbr, 11, 10)

RETURN @PhoneFormatted
END
END 

Auguy
Sylvania/Toledo Ohio

RE: Using UDF In Select, Bad Idea?

for the latest SQL Server version its not as bad as before - but still you should instead consider using a ITVF function - see some details at https://www.sqlservercentral.com/articles/creating...

in the case above you could create and use the function as follows

CODE

create FUNCTION [dbo].[Udf_FormatPhoneNumber](@PhoneNbr VARCHAR(20))
RETURNS table
AS
return
select case when LEN(@PhoneNbr) < 10
    then @PhoneNbr
 else LEFT(@PhoneNbr, 3) + '-' + SUBSTRING(@PhoneNbr, 4, 3) + '-' + SUBSTRING(@PhoneNbr, 7, 4) + ' ' + SUBSTRING(@PhoneNbr, 11, 10)
 end as PhoneFormatted


/*
 and you would then use it as if it was another table on your sql statements using either one of the APPLY statements or a join depending on what your ITVF function does - in this case a cross apply is the best one
*/

select *
from (values ('1234567890123')
           , ('123456789')
     ) t(phonenumber)
cross apply Udf_FormatPhoneNumber(t.phonenumber) fp 

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Using UDF In Select, Bad Idea?

How about having an additional, computed column in your table where you keep the phone number properly formatted.... ponder

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Using UDF In Select, Bad Idea?

(OP)
Thanks to both of you, I will check out the ITVF.
Any, I may just do that, space is not a consideration here.

Auguy
Sylvania/Toledo Ohio

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close