SQL SERVER – PASSING A TABLE COLUMN TO A TABLE VALUED FUNCTION

SQL SERVER – PASSING A TABLE COLUMN TO A TABLE VALUED FUNCTION

Today at 3:20 pm, One of my friend Ajith vargheese came for an Urgent help.This Question is Can we pass a table column to Table valued function?The answer is yes.This can be achieved through CROSS APPLY a new Operator in SQL SERVER 2005 and 2008; But unfortunately it is Not in SQL SERVER 2000.What is Cross Apply? Cross Apply is same as  Inner join to table valued function which can take parameters. This operation is not possible to do using regular JOIN syntax

Look at this Example

I have two tables TableA and TableB, where TableA contains

Name of an Employees and TableB Contains the address code of the   Employees,both can be joined by ID and ID2

Here I am going to create a Table valued Function which will return ID and Address of the TableB .Look at this Function for more details

CREATE FUNCTION Udf_table_addresslisting(@ID INT)
RETURNS @table TABLE (
id INT,
address VARCHAR(50))
AS
BEGIN
INSERT INTO @table
SELECT id2,
address
FROM tableb
WHERE id2 = @ID
RETURN
END

Now I am using the CrossApply operator to fetch the Details.

SELECT [Name],
[Address]
FROM tablea AS a
CROSS APPLY dbo.Udf_table_addresslisting(a.id) AS b

Ajith thanks for asking this wonderfull Question.