Can we call function inside an function SQL Server 2005

Question

ALTER function [dbo].[getEmployeeID](@ID int) returns table
as  
  begin
  return (
    select * from [dbo].[gtEmployeeName](2)
    select * from Employees where EmployeeID = @ID)
end

here [dbo].[gtEmployeeName] is an other function that I am trying to call.

I am getting an error, can we call or is there any syntax problem?

Msg 156, Level 15, State 1, Procedure getEmployeeID, Line 6
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Procedure getEmployeeID, Line 6
Incorrect syntax near ')'.

Thanks Prince

Answer 1

If [dbo].[gtEmployeeName] returns scalar you probably are looking for

ALTER function [dbo].[getEmployeeID](@ID int) returns table
as  
begin
return (
    select *, [dbo].[gtEmployeeName](2) as EmpName from Employees where EmployeeID=@ID)
end

If [dbo].[gtEmployeeName] returns table you probably are looking for

ALTER function [dbo].[getEmployeeID](@ID int) returns table
as  
begin
return (
    select * from [dbo].[gtEmployeeName](2) EN
    inner join Employees E on EN.EmployeeID = E.EmployeeID
    where EmployeeID=@ID)
end

Update the join to outer if that is what you need. Also update the join condition (the example assumes that the returned table from gtEmployeeName has a column EmployeeID and that can be used for joining to Employees.