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')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|||/*
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
*/
