Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I just created a UDF and testing it I found only works when I use this syntax

SELECT [PMIS].[dbo].[fnIsReportingTo] (50,1132)

I was expecting it to work like this:

select * from fnIsReportingTo (50,1132)


select fnIsReportingTo (50,1132)

here is my create SQL:

create function fnIsReportingTo
@BossID int,
@EmployeeID int)
declare @ret bit
    if exists (select * from dbo.fnGetEmployeeHierarchy(@BossID) where employeeID=@employeeID)
        select @ret=1
        select @ret=0
return @ret
share|improve this question
up vote 8 down vote accepted

Scalar UDFS must be qualified with schema. You don't need the database part of the qualified name unless it's in a different database of course.

SELECT [dbo].[fnIsReportingTo] (50,1132)

In the FROM clause, you can only use table valued functions

See "Types of Functions" in MSDN

Edit: As an observation, I'd tend to avoid:

  • nesting UDFs this
  • using table lookups in scalar UDFs

if you use this function for each row of another table you have a cursor that iterates over the table in the UDF for every outer row

Even this is better. It's now table valued and expands like a view.

create function fnIsReportingTo (
    @BossID int,
    @EmployeeID int)
RETURNs table
return (
      select CAST(COUNT(*) AS bit) AS FooBar
      from dbo.fnGetEmployeeHierarchy(@BossID)
     where employeeID=@employeeID)
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.