A new feature of Microsoft SQL Server 2005 is "CROSS APPLY". It restricted "INNER JOIN" between a table (outer query) and a table-valued function (common usage), or derived table from correlated subquery. The table-valued function is evaluated only for the parameter values supplied by the outer query.
The result of the CROSS APPLY quay can be achieved by using temporary table, CTE or Table Variable. But CROSS APPLY provides a very powerful solution in a single query.
Let's takes an example, which can help us to build the concepts of CROSS APPLY. Our motto is to supply the result of outer query as a parameters value of table value function.
[ Outer Query] à result of outer query à [ Parameters value of Table valued Function]
Step-1 [ Create the Table Objects ]
CREATE TABLE People_details
(
PersonID int NOT NULL,
MotherID int NULL,
FatherID int NULL,
Name varchar(50) NOT NULL,
CONSTRAINT PK_People PRIMARY KEY(PersonID),
)
GO
Step-2 [ Insert Values Into Table Objects ]
INSERT INTO People_details
VALUES(1 , NULL, NULL, 'Rajesh Das')
INSERT INTO People_details
VALUES(2 , NULL, NULL, 'Raja Barma')
INSERT INTO People_details
VALUES(3 , NULL, NULL, 'Anand Kimmel')
INSERT INTO People_details
VALUES(4 , NULL, NULL, 'Sajan Kimmel')
INSERT INTO People_details
VALUES(5 , 2, 1, 'Joga Benavides')
INSERT INTO People_details
VALUES(6 , 3, 4, 'Giban Kimmel')
INSERT INTO People_details
VALUES(7 , 5, 6, 'Kalyan Hemenway')
INSERT INTO People_details
VALUES(8 , 5, 6, 'Dinesh Kimmel')
INSERT INTO People_details
VALUES(9 , 5, 6, 'Deb Kimmel')
INSERT INTO People_details
VALUES(10 , 5, 7, 'Rohit Benavides')
INSERT INTO People_details
VALUES(11 , 5, 7, 'Nitin Benavides')
INSERT INTO People_details
VALUES(12 , 5, 6, 'Jana Kimmel')
INSERT INTO People_details
VALUES(13 , 5, 6, 'Palu Kimmel')
INSERT INTO People_details
VALUES(14 , NULL, NULL, 'Logar Kimmel')
INSERT INTO People_details
VALUES(15 , NULL, NULL, 'David Benavides')
INSERT INTO People_details
VALUES(16 , 14, 13, 'Alex Kimmel')
INSERT INTO People_details
VALUES(17 , 14, 13, 'Noah Kimmel')
SELECT * FROM People_details
Step-3 [ The T-SQL Statements that I used in Function ]
SELECT p1.Name as MyName,
p2.Name AS Mother,
p3.Name As Father
FROM People_details p1
LEFT JOIN People_details p2 ON p1.MotherID = p2.PersonID
LEFT JOIN People_details p3 ON p1.FatherID = p3.PersonID
Step-4 [ Create the Function ]
CREATE FUNCTION [fnGetParents](@PersonID int)
RETURNS @Parents TABLE
(
[PersonID] [int] PRIMARY KEY NOT NULL,
[Self] [varchar](25),
[Mother] [varchar](25) NULL,
[Father] [varchar](25) NULL
)
AS
BEGIN
INSERT INTO @Parents
SELECT
p1.PersonID,
p1.Name AS [Self],
p2.[Name] AS Mother,
p3.[Name] AS Father
FROM
People_details p1
INNER JOIN People_details p2 ON p1.MotherID = p2.PersonID
INNER JOIN People_details p3 ON p1.FatherID = p3.PersonID
WHERE
p1.PersonID = @PersonID;
RETURN;
END;
Step-5 [ This Query give you an error that's why we have to use CROSS APPLY ]
SELECT p1.PersonID, p1.Name, dbo.fnGetParents(p1.PersonID)
FROM People_details p1
Step-6 [ The CROSS APPLY ]
SELECT p1.PersonID, p1.Name, p2.Mother, p2.Father
FROM People_details p1
CROSS APPLY fnGetParents(p1.PersonID)p2
Analyzing the Step-6 that is the CROSS APPLY
[ Outer Query] à result of outer query à [ Parameters value of Table valued Function]
SELECT p1.PersonID, p1.Name, p2.Mother, p2.Father
FROM People_details p1
CROSS APPLY fnGetParents(p1.PersonID)p2
Lets brake down the query into parts to understand it properly as above definition of CROSS APPLY says.
Here the Outer Query is
SELECT p1.PersonID, p1.Name, p2.Mother, p2.Father
FROM People_details p1
The Result of the Outer Query is
p1.PersonID
Parameters value of the Table valued Function is
fnGetParents(p1.PersonID)p2
I hope you now you understand it and thanking you to provide your valuable time on it.
Posted by: MR. JOYDEEP DAS
No comments:
Post a Comment