CODEWITHSUNDEEP
sql-server-2005
happysmile
happysmile

Reputation: 7777

Can we call function inside an function SQL Server 2005

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

Upvotes: 5

Views: 32366

Answers (2)

amit_g
amit_g

Reputation: 31250

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.

Upvotes: 7

Curtis
Curtis

Reputation: 103358

Yes you can call a function inside a function.

In fact, you can call the current function inside the function, to cause a loop.

What error are you getting? Your error is most likely related to something else

Upvotes: 3

Related Questions