CODEWITHSUNDEEP
t-sqlsql-server-2012
ejmtv
ejmtv

Reputation: 198

Cannot execute user defined function UDF in SQL Server

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?

Upvotes: 0

Views: 1227

Answers (2)

ppijnenburg
ppijnenburg

Reputation: 153

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

Upvotes: 1

ejmtv
ejmtv

Reputation: 198

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

I had to type dbo.displayName

Upvotes: 0

Related Questions