Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1

    Question Unanswered: Reference a column in UDF in a FROM or WHERE clause

    Hello there,

    I got a problem with using a table returning UDF in
    FROM/WHERE clause.

    For example...

    SELECT *
    FROM tb_Employees AS E INNER JOIN
    dbo.fn_EmployeesInDept('Houskeeping') AS EID ON
    E.EmployeeID = EID.EmployeeID

    Works without a problem. But when i reference a column in
    the udf...

    SELECT *
    FROM tb_Employees AS E INNER JOIN
    dbo.fn_EmployeesInDept(E.Department) AS EID ON
    E.EmployeeID = EID.EmployeeID

    Gives me the following error message

    Server: Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near '.'.

    Joining in the WHERE Clause gives the same Message

    NOTE. This is a simplified example of what i'm actally
    trying to achieve.
    The point is referencing a column in a UDF in a FROM or
    WHERE clause

    Why is this? Thanks

    CREATE TABLE [tb_employees]
    (
    [EmployeeID] [int] NOT NULL ,
    [EmployeeName] [nvarchar] (50) ,
    [Department] [nvarchar] (50) ,
    CONSTRAINT [PK_tb_employees] PRIMARY KEY
    CLUSTERED
    (
    [EmployeeID]
    ) ON [PRIMARY]
    ) ON [PRIMARY]

    CREATE FUNCTION fn_EmployeesInDept (@Department nvarchar
    (50))
    RETURNS @Employees TABLE
    (
    EmployeeID int
    )
    AS
    BEGIN
    INSERT INTO @Employees(EmployeeID) SELECT
    EmployeeID FROM tb_employees WHERE Department = @Department

    RETURN
    END

    INSERT INTO tb_Employees(EmployeeID, EmployeeName,
    Department) VALUES (1, 'Kees', 'Shipping')
    INSERT INTO tb_Employees(EmployeeID, EmployeeName,
    Department) VALUES (2, 'Piet', 'Shipping')
    INSERT INTO tb_Employees(EmployeeID, EmployeeName,
    Department) VALUES (3, 'Jan', 'Accounting')
    INSERT INTO tb_Employees(EmployeeID, EmployeeName,
    Department) VALUES (4, 'Klaas', 'Accounting')
    INSERT INTO tb_Employees(EmployeeID, EmployeeName,
    Department) VALUES (5, 'Dirk', 'Houskeeping')
    INSERT INTO tb_Employees(EmployeeID, EmployeeName,
    Department) VALUES (6, 'Arie', 'Houskeeping')
    INSERT INTO tb_Employees(EmployeeID, EmployeeName,
    Department) VALUES (7, 'Bob', 'Houskeeping')

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: Reference a column in UDF in a FROM or WHERE clause

    RE:
    Q1 [Is it possible to pass a UDF a non-scalar as in the example in the From clause; and why]? Example in the From clause:

    SELECT *
    FROM tb_Employees AS E INNER JOIN
    dbo.fn_EmployeesInDept(E.Department) AS EID ON
    E.EmployeeID = EID.EmployeeID
    A1 Not directly. Sql Server 2k UDFs parameters currently may accept constants (or certain kinds of expressions evaluateing to scalar constants within the current execution context). Sql Server 2k UDFs parameters do not currently accept table, or other non scalar referenced database objects. One may instead use an iterative approach, (passing each sucessive value in as a scalar) as for example:

    Declare
    @vDepartment As Nvarchar (128)
    Select @vDepartment = (Select Top 1 Department From dbo.tb_Employees)
    Select @vDepartment As '@vDepartment', EmployeeID From dbo.fn_EmployeesInDept(@vDepartment)
    .
    .
    .


    RE:
    Q2 [Is it possible to reference a column in a UDF in a Where Clause]?
    A2 Yes. Referencing Sql Server 2k UDFs in a Where clause is supported. The following, for example, should work:

    SELECT *
    FROM tb_Employees AS E INNER JOIN
    dbo.fn_EmployeesInDept('Houskeeping') AS EID ON
    E.EmployeeID = EID.EmployeeID
    Where EID.EmployeeID > 5

  3. #3
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    /*
    USE SCALAR FUNCTION
    */
    create FUNCTION fn_IsEmployeeInDept (@Department nvarchar (50), @EmployeeID int)
    RETURNS bit
    with schemabinding
    AS
    BEGIN
    declare @IsEmployeeID bit
    if exists(select Department from dbo.tb_employees WHERE Department = @Department and EmployeeID=@EmployeeID)
    set @IsEmployeeID=1
    else
    set @IsEmployeeID=0
    RETURN @IsEmployeeID
    END
    GO

    /*
    HAS DIFFERENT FUNCTIONALITY
    */
    SELECT E.EmployeeID,E2.EmployeeID
    FROM tb_Employees AS E
    JOIN tb_Employees AS E2 on fn_IsEmployeeInDept(E.Department,E2.EmployeeID)=1

    /*
    IT IS NOSENCE, CAN BE REPLACED BY JOIN
    */
    SELECT E.EmployeeID,E2.EmployeeID
    FROM tb_Employees AS E
    JOIN tb_Employees AS E2 on E.Department=E2.Department

    /*
    BUT SCALAR FUNCTION JOIN CAN USED TO JOIN HIERARCHIES IN MSSQL2K
    */

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •