May 23, 2013 at 11:25 am
Hey guys -
I'm trying to create a custom Function in SQL 2012 (by following an example in a book) and it's not working. I'm hoping someone can help.
This is my custom function. No errors. It executes fine. Simple right?
**********************************
CREATE FUNCTION fncNotAvailableDisplay
(@strInputString varchar(50))
RETURNS
varchar(20)
AS
BEGIN
If @strInputString IS NULL
SET @strInputString = 'Not Available'
RETURN @strInputString
END
**************************
When I try to use it in a query, I get an error.
SELECT Address1, Address2, fncNotAvailableDisplay(Address3) FROM Customers
'fncNotAvailableDisplay' is not a recognized built-in function name.
I am using it within the same database. Thoughts??
May 23, 2013 at 11:33 am
i believe functions that are not CLR functions must be prefaced by the schema:
SELECT
Address1,
Address2,
dbo.FNCNOTAVAILABLEDISPLAY(Address3)
FROM Customers
Lowell
May 23, 2013 at 11:43 am
Yep. That was it. Thanks!
May 23, 2013 at 12:35 pm
I would like to point out that this function could be a performance killer if used with large data sets. There are two ways to fix this.
One, instead of the function in the select list you could do this:
SELECT
Address1,
Address2,
coalesce(Address3,'Not Available') as Address3
FROM
Customers
Or you could rewrite the function like this:
DROP FUNCTION dbo.fncNotAvailableDisplay;
go
CREATE FUNCTION dbo.fncNotAvailableDisplay
(@strInputString varchar(50))
RETURNS TABLE
AS
RETURN select coalesce(@strInputString,'Not Available') as NAVDisplay
go
And then use it like this:
SELECT
Address1,
Address2,
nad.NAVDisplay as Address3
FROM
Customers
CROSS APPLY dbo.fncNotAvailableDisplay(Address3) nad
May 23, 2013 at 1:30 pm
+100 to Lynn!!
Read my chapter in the SQL Server MVP Deep Dives 2 book entitled "Death by UDF" 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply