User Defined Functions - Northwind Database - Employee_Name function

Question

I am working on an assignment and found some trouble. Here is the assignment:

This assignment will use the northwind database and the Employees table.

This will be a table user defined function.

Create a user defined function named Employee_Name. Pass an Employee Id as a parameter.
Return the First Name, Last Name, and Phone Number of the employee whose ID matches the id passed to the user defined function.
Create a query that uses the function and returns information for the employee with an id of 5.

Here is my solution so far:

CREATE FUNCTION dbo.Employee_Name(@employId int)
RETURNS TABLE
AS 
RETURN(
    SELECT FirstName,LastName,HomePhone 
    FROM dbo.Employees 
    WHERE EmployeeID = @employId
    GROUP BY EmployeeID
)

SELECT * FROM dbo.Employee_Name(5);
GO

Here is the error I'm receiving:

Msg 156, Level 15, State 1, Procedure Employee_Name, Line 11
Incorrect syntax near the keyword 'SELECT'.

Just a guess, but you might have to create a temp table in your function, populate it, then return it. — Dan Bracuk
– Dan Bracuk, Commented Nov 24, 2013 at 21:37
Northwind is a sample for Microsoft SQL Server. Is the MySQL tag appropriate on this question? — O. Jones
– O. Jones, Commented Nov 24, 2013 at 22:07
@DanBracuk I thought you cannot use a temp table inside of UDF? — Comfortably Numb
– Comfortably Numb, Commented Nov 24, 2013 at 23:00

KekuSemau · Accepted Answer · 2013-11-24 23:19:20Z

1

You have to seperate creating the function and using it. Run the CREATE statement before you start working on #3.
Technically, you can also just insert a GO before the final SELECT in your sample code and it should work alright.

answered Nov 24, 2013 at 23:19

KekuSemau

6,8434 gold badges27 silver badges37 bronze badges

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

Comments

Comfortably Numb · Accepted Answer · 2013-11-24 23:24:23Z

1

You function is not created because the SELECT statement:

SELECT FirstName,LastName,HomePhone 
FROM dbo.Employees 
WHERE EmployeeID = @employId
GROUP BY EmployeeID

is incorrect (it inludes 3 fields in the SELECT and one, completely different in GROUP BY).

In fact you don't even need a GROUP BY, since EmployeeID is a unique value. Just create your function as

CREATE FUNCTION dbo.Employee_Name(@employId int)
RETURNS TABLE
AS 
RETURN(
    SELECT FirstName,LastName,HomePhone 
    FROM dbo.Employees 
    WHERE EmployeeID = @employId
)

then your SELECT * FROM dbo.Employee_Name(5) will work.

And yes as @KekuSemau pointed out, either run CREATE FUNCTION and your SELECT * ... as separate queries or insert GO between them - CREATE has to be in a separate batch.

answered Nov 24, 2013 at 23:24

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

1 Comment

KekuSemau Over a year ago

Indeed, I didn't even see that GROUP BY at all!

C B · Accepted Answer · 2017-12-14 21:25:10Z

-1

Try This:

CREATE FUNCTION dbo.Employees_Name(@employeeID int)
Returns Table
AS
Return(
   SELECT FirstName,LastName,PhoneNumber
   FROM dbo.Employees
   WHERE EmployeeID = @employID
   )

   GO
   SELECT * FROM dbo.Employees_Name(5)

edited Dec 14, 2017 at 21:25

C B

1,7176 gold badges20 silver badges20 bronze badges

answered Dec 14, 2017 at 20:36

user9100823

1

Collectives™ on Stack Overflow

User Defined Functions - Northwind Database - Employee_Name function

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related