Using UDF In Select, Bad Idea? 2

Auguy · Jan 7, 2023

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

fredericofonseca · Jan 8, 2023

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-and-using-inline-table-valued-functions

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

Andrzejek · Jan 9, 2023

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

Auguy · Jan 9, 2023

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Using UDF In Select, Bad Idea? 2

Auguy

Programmer

fredericofonseca

IS-IT--Management

Andrzejek

Programmer

Auguy

Programmer

Similar threads

Part and Inventory Search

Sponsor