Cannot execute user defined function UDF in SQL Server

Question

I am creating a function to return the full name of a customer when a customer id is entered:

CREATE FUNCTION displayName
(
    @customer_id int
)
RETURNS varchar(50)
AS
BEGIN
    DECLARE @name varchar(50)
    SELECT @name = (SELECT name_first + ' ' + name_last AS FULL_NAME FROM DT_CUSTOMERS WHERE customer_id = @customer_id)
    RETURN @name
END
GO

Is something wrong with my code? How do I run it?

1. how do you use the function? functions can be used as part of the SELECT clause of a query, e.g. SELECT dbo.displayName(42) 2. if you concatenate NULL with string, the result is NULL, so if either first_name or last_name are null, the result is NULL. — Tsahi Asher
– Tsahi Asher, Commented Jun 1, 2017 at 9:11
I always get this error Msg 195, Level 15, State 10, Line 1 'displayName' is not a recognized built-in function name. i've created many versions with different names — ejmtv
– ejmtv, Commented Jun 1, 2017 at 9:19

ppijnenburg · Accepted Answer · 2017-06-01 09:45:15Z

1

Common practice is to define in which DB you are going to run the query (you might be in the wrong db where you don't have any rights?) and which schema you want to add to it: When executed like this, it should work.

USE
databasename_here
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.displayName
(
    @customer_id int
)
RETURNS varchar(50)
AS
BEGIN
    DECLARE @name varchar(50)
    SELECT @name = (SELECT name_first + ' ' + name_last AS FULL_NAME FROM DT_CUSTOMERS WHERE customer_id = @customer_id)
    RETURN @name
END
GO

It should be executed as follows:

SELECT 
   dbo.displayName(column_which_contains_id) 
from db.schema.table

edited Jun 1, 2017 at 9:45

answered Jun 1, 2017 at 9:31

ppijnenburg

1632 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ejmtv · Accepted Answer · 2017-06-01 09:28:48Z

0

it doesn't work in my case without the user.

I had to type dbo.displayName

answered Jun 1, 2017 at 9:28

ejmtv

1985 silver badges20 bronze badges

Collectives™ on Stack Overflow

Cannot execute user defined function UDF in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related