Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given the following definitions:

CREATE TABLE MyTable
(
    col1 int,
    col2 int,
    col3 int,
    col4 int
)

CREATE FUNCTION fn_MyFunction
(
    @Param1 int,
    @Param2 int
)
RETURNS TABLE AS RETURN
(
    SELECT @Param1 * 2 AS 'res1', @Param2 * 4 AS 'res2', @Param1 AS 'col3'
)

I am trying to join the function to the table so that I get a calculated value (s) per row. Example: In a bookings table, I need to get the price based on a start date and end date.

Here is an example query:

SELECT      tbl.col1
,           tbl.col2
,           tbl.col3
,           fn.res1
,           fn.res1
FROM        MyTable tbl
CROSS APPLY fn_MyFunction(tbl.col3, tbl.col4) fn
WHERE       fn.col3 = tbl.col3

While I think I am getting the correct results in my actual query, how does the CROSS APPLY get the results in this case? Does it get them row-by-row (effectively)? Or does it work in a similar way to CROSS JOIN (ie: making a Cartesian Product)?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

CROSS APPLY takes a table valued function and 'applies' parameters from each row in the query you are applying it to. The function is evaluated once for each row and the output is implicitly joined to the source row in the record set from which the parameters were obtained. Note that this 'join' can be 1:M - with a TVF one row at source can generate multiple rows of output from the TVF.

You don't need to use any more predicates in the where clause to join the results unless (for some reason) you want to further filter the output of the function by applying an additional predicate.

CROSS APPLY is quite useful with xpath queries if you have to go ferreting around XML fields that may contain repeating groups.

share|improve this answer
    
+1 - It's also useful for calculated fields that you want to reference more than once - you can use the alias instead of the whole definition. –  JNK Apr 4 '12 at 14:46
    
@JNK - long time no see in the heap. –  ConcernedOfTunbridgeWells Apr 4 '12 at 15:07
    
Yeah I got busy, (un)fortunately... –  JNK Apr 4 '12 at 15:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.