Cross Apply in inline table valued function in SQL Server 2005

CROSS APPLY and OUTER APPLY is new in Microsoft SQL Server 2005. CROSS APPLY works like JOIN and a.k.a. INNER APPLY. You can use it like co-related query.

–create one table for demo

use adventureworks

–department table

CREaTE TABLE deptInfo

(

DeptName VARCHAR(10),

Description VARCHAR(20)

)

–INSERT records

INSERT INTO DeptInfo

SELECT ‘MIS’,‘IT DEPT’ UNION ALL

SELECT ‘account’,‘finance department’ UNION ALL

SELECT ‘chemical’,‘chemical department’

GO

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–creating inline table valued function

CREATE FUNCTION dbo.GetEmployeeData(@dept VARCHAR(10))

RETURNS TABLE

AS

RETURN

(

SELECT * FROM emps WHERE dept=@dept

)

GO

–use cross apply in above function

SELECT d.Deptname,d.description,e.name,e.company FROM DeptInfo d

CROSS APPLY

dbo.getemployeedata(d.deptname) as e

WHERE d.deptname=‘MIS’

Note: You can refer another article on Cross Apply and Outer apply at:

http://www.sqlhub.com/2009/03/cross-apply-and-outer-apply-clause-in.html

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: