CODEWITHSUNDEEP
sqlsql-server
norris1023
norris1023

Reputation: 633

Working with user-defined Function

I am working on something I am trying to create a user defined function named Employee_Name. Pass an Employee Id as a parameter.

This is what I have so far :

Create function Employee_name (
@EmployeeID int 
 )
 Return Table
 as 
 Return(
         Select * From Employees
    Where Employee_name = @EmplyeeId)
 go
 SELECT * FROM Employee_name

But I am not thinking I am doing this right because where the return table is mark with red. Which means an error. I am still learning this, its kinda confusing for me. I am woundering if the return table should be only a return.

Upvotes: 0

Views: 234

Answers (3)

jon Z
jon Z

Reputation: 16646

Return Table should be Returns Table

Upvotes: 2

Royi Namir
Royi Namir

Reputation: 148734

change it to : 

Create function Employee_name (
@EmployeeID int 
 )
 Returns Table
 as 
 Return(
         Select * From Employees
    Where Employee_name = @EmplyeeId)
 go

and in code : 

select * from Employee_name (1)

Upvotes: 1

Russ Cam
Russ Cam

Reputation: 125538

You need to pass an Employee ID to your user defined function

SELECT * FROM Employee_name (1)

Also it should be RETURNS TABLE. So

CREATE FUNCTION Employee_name 
(
    @EmployeeID int 
)
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM Employees
    WHERE Employee_name = @EmployeeID
)
GO

SELECT * FROM Employee_name (1)

You might have some naming collision between the name of your function and the name of the field in the Employees table though (both appear to be called Employee_name)

Upvotes: 1

Related Questions