How Do I call a UDF in a case statement inside a UDF

Question

Post reply

How Do I call a UDF in a case statement inside a UDF

MothInTheMachine

Hall of Fame

Points: 3251
More actions
July 29, 2009 at 9:04 am

#100654

Hi Folks,
Perhaps someone can suggeste a better way to accomplish this. I have a UDF that displays an address that is used for driving purposes. If address2 contains a value like 'PO Box 123' I want to concatenate a space rather than the value. I was thinking that I could make a udf that would call the table and use something like
set @returnvalue = false
if LEFT(address2, 6) = 'PO Box'
@returnvalue = true
but this doesn't work, I think because the return value is not the same datatype as address2 in my case statement. This is how I am calling the second function from my udf:
SELECT @ClassName = Address1 + ' ' +
CASE Address2 when null then ''
when dbo.isPOBox(Address2) then ''
else Rtrim(Address2)
END
+ '
' + dbo.ProperCase(City) + ',' + State + ' ' + ZipCode
from blah
That line, when dbo.isPOBox(Address2) then '' obviously doesn't do the trick since I am returning a varchar True and false and not booleans, which would violate the rules of the case statement. here is my funciton for isPOBox:
create function isPOBox(@Address2 as varchar(50))
returns varchar(5)
as
begin
declare @Ret varchar(5);
declare @i varchar(50);
if Left(rtrim(lTrim(@Address2)),6) = 'PO Box'
set @Ret = 'true'
else
set @Ret = 'false'
return @Ret
end
[\code]
Any ideas?
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Why not just use

Case WHen Address2 like '%PO Box%' Then '' Else Address2

?

This is set based and will perform much better than a UDF which will have to be called for every row.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

I think Jack and I are on the same track today..

You need to be careful about nesting UDF use with UDFs, because of their use they can make a big impact on the performance of a query. A UDF used in a query is called once for EVERY row, if it has to goto a second UDF it will also get called for every row (probably).

CEWII

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 3

I didn't know that syntax existed. I'll give it a whirl.

Thanks!!

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;