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.
I also have a UDF
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
Talk To Other Members
RE: Using UDF In Select, Bad Idea?
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) fpRegards
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?
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Using UDF In Select, Bad Idea?
Any, I may just do that, space is not a consideration here.
Auguy
Sylvania/Toledo Ohio