Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating a Custom Function (Scalar) Expand / Collapse
Author
Message
Posted Thursday, May 23, 2013 11:25 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
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??
Post #1456123
Posted Thursday, May 23, 2013 11:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 12,923, Visits: 32,313
i believe functions that are not CLR functions must be prefaced by the schema:
SELECT
Address1,
Address2,
dbo.FNCNOTAVAILABLEDISPLAY(Address3)
FROM Customers



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1456128
Posted Thursday, May 23, 2013 11:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
Yep. That was it. Thanks!
Post #1456133
Posted Thursday, May 23, 2013 12:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 20,801, Visits: 32,730
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1456167
Posted Thursday, May 23, 2013 1:30 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 4,437, Visits: 6,339
+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 at GMail
Post #1456199
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse