SQL SERVER - PASSING A TABLE COLUMN TO A TABLE VALUED FUNCTION | SQLINFO

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

January 5, 2011January 5, 2011Varun R

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.

6 thoughts on “SQL SERVER – PASSING A TABLE COLUMN TO A TABLE VALUED FUNCTION”

ANEESH
14 years ago

You really solved one of my greatest hecks…. thanks
shahiz
14 years ago

Thanks varun…
this is very helpful and intresting………
John Rob
14 years ago

This is best one article so far I have read online. I would like to appreciate you for making it very simple and easy. I have found another nice post related to this post over the internet which also explained very well. For more details please check out this link…

Table Valued Function in SQL Server

Thanks
veera
14 years ago

i have one doubt.please help me.
i have 2 tables. one is emp table another table is applicant table.
emp table having emp_id is identity column. applicant table having app_id is identity column. one applicant come for the interview. that is one to one interview.now i’m storing applicant table in empid(1) varchar datatype. second time same applicant come but that is group interview 3 employees attending that applicant. now i’m storing applicant table in empid(1,2,3)
my question is
how to join two tables to retrieve employee name
android phone reviews
14 years ago

I really like your blog.. very nice colors &
theme. Did you design this website yourself or did you hire someone
to do it for you? Plz respond as I’m looking to construct my own blog and would like to find out where u got this from. cheers
Swapnali
14 years ago

Thanks , it worked for me too..