Take the 2-minute tour ×
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. It's 100% free, no registration required.

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)

or

select fnIsReportingTo (50,1132)

here is my create SQL:

create function fnIsReportingTo
(
@BossID int,
@EmployeeID int)
RETURNs bit
AS
begin
declare @ret bit
    if exists (select * from dbo.fnGetEmployeeHierarchy(@BossID) where employeeID=@employeeID)
        select @ret=1
    else
        select @ret=0
return @ret
end
share|improve this question
add comment

1 Answer

up vote 7 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
AS
return (
      select CAST(COUNT(*) AS bit) AS FooBar
      from dbo.fnGetEmployeeHierarchy(@BossID)
     where employeeID=@employeeID)
GO
share|improve this answer
add comment

Your Answer

 
discard

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.