How to join a table-valued Function

Question

How to join a table-valued Function

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Mike C SSC-Insane Points: 23224 More actions · Answer 1

Sergiy (10/17/2007)
Mike, it would be nice if you'd pay some attention to the name of the forum.
It's on top of the page, between "Home" and "T-SQL".
There is no point to repeatedly post SQL2005 solution not appropriate to SQL2000 on this forum.

Thank you for the constructive feedback Sergiy. I was, however, responding to questions from another poster who asked for specific examples after I mentioned that it was possible in SQL 2005 with censored. I sincerely apologize if my answering someone else's question has caused you undue stress.

To show my sincerity, and to bring this conversation to a complete and total end, I've removed all references to non-SQL 2000-specific sample code, methods, and keywords from all of my posts in this thread. I sincerely hope that relieves your anxiety.

Thanks again!

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Mike C (10/17/2007)
Sergiy (10/17/2007)
Mike, it would be nice if you'd pay some attention to the name of the forum.
It's on top of the page, between "Home" and "T-SQL".
There is no point to repeatedly post SQL2005 solution not appropriate to SQL2000 on this forum.
Thank you for the constructive feedback Sergiy. I was, however, responding to questions from another poster who asked for specific examples after I mentioned that it was possible in SQL 2005 with censored. I sincerely apologize if my answering someone else's question has caused you undue stress.
To show my sincerity, and to bring this conversation to a complete and total end, I've removed all references to non-SQL 2000-specific sample code, methods, and keywords from all of my posts in this thread. I sincerely hope that relieves your anxiety.
Thanks again!

Mike,

First of all I never requested to remove anything from you posts. Don't lie.

Second, there was no any anxiety from my side, only some nervous reaction from yours.

I'm not a doctor, don't know what to suggest to relieve your anxiety.

Third, you posted same code 3 times. For what reason? Trying to prove yourself insisting on your suggestion?

Bad call, because

Forth, CROSS APPLY sucks in terms of performance, as most of the features introduced in SQL2005. Try to compare it with simple view instead of that table function and see the strong reason why CROSS APPLY should never be used.

_____________
Code for TallyGenerator

Alec Griffiths-324451 Valued Member Points: 61 More actions · Answer 3

The problem is a google search such as '"table function" "sql server" join parameter' returns this post and I am using SQL server 2005. Would be nice if there was a link from here to where the post should be..

J-440512 SSCertifiable Points: 6285 More actions · Answer 4

Sergiy (10/17/2007)
Mike,
unfortunately there is no such thing as "CROSS APPLY" in MS SQL 2000.

In MS SQL Server 2000, you can get a cartesian product simply like this

[font="Courier New"][font="Arial"]create table TableA (pk int not null primary key identity, AVal varchar(10))

create table TableB (pk int not null primary key identity, BVal varchar(10))

insert into TableA (AVal) values ( 'A1')

insert into TableA (AVal) values ( 'A2')

insert into TableA (AVal) values ( 'A3')

insert into TableB (BVal) values ( 'B 100')

insert into TableB (BVal) values ( 'B 200')

insert into TableB (BVal) values ( 'B 300')

insert into TableB (BVal) values ( 'B 400')

SELECT A.Aval, B.BVal

FROM TableA A, TableB B

Aval BVal

A1 B 100

A2 B 100

A3 B 100

A1 B 200

A2 B 200

A3 B 200

A1 B 300

A2 B 300

A3 B 300

A1 B 400

A2 B 400

A3 B 400

(12 row(s) affected)[/font][/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

J (4/2/2008)
Sergiy (10/17/2007)
Mike,
unfortunately there is no such thing as "CROSS APPLY" in MS SQL 2000.
In MS SQL Server 2000, you can get a cartesian product simply like this

J -

Note that he was talking about cross APPLY, not cross JOIN. CROSS APPLY is used to call table-valued functions based on column values. It is essentially a fancy implicit version of correlated sub-queries, not the Cartesian Product as done with CROSS JOIN.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

J-440512 SSCertifiable Points: 6285 More actions · Answer 6

Matt,

I stand corrected. Thank you.

I have not made the switch to 2K5 and do not plan to, if it is at all possible to jump directly from 2K to 2K8.

Regards

lalit.madan Valued Member Points: 68 More actions · Answer 7

/*this behaves like inner join*/

select * from freight_manager

cross apply LargeOrderShippers ( freight_manager.min_value, freight_manager.max_value)

/*this behaves like cross join*/

select * from freight_manager

outer apply LargeOrderShippers ( freight_manager.min_value, freight_manager.max_value)

🙂

awadhesh.singh SSC Enthusiast Points: 115 More actions · Answer 8

Mike answer is perfect answer . When you want to use join between a table and table valued function you can choose either outer apply or cross apply as per your requirement.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

lalit.madan (7/19/2011)
/*this behaves like inner join*/
select * from freight_manager
cross apply LargeOrderShippers ( freight_manager.min_value, freight_manager.max_value)
/*this behaves like cross join*/
select * from freight_manager
outer apply LargeOrderShippers ( freight_manager.min_value, freight_manager.max_value)
🙂

I think you will find OUTER APPLY behaves more like LEFT (OUTER) JOIN.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sanajmshaji SSC Journeyman Points: 86 More actions · Answer 10

Hi Guys

Please refer the below link.

http://www.java2s.com/Tutorial/SQLServer/0080__Table-Join/UsingAPPLYtoInvokeaTableValuedFunctionforEachRow.htm

Step 1

---------------------------------------------------------------------

CREATE FUNCTION dbo.fn_getByID(@ID int) RETURNS TABLE

AS

RETURN SELECT first_name FROM employee WHERE ID = @ID

GO

---------------------------------------------------------------------

Step 2

SELECT w.last_name FROM employee w

CROSS APPLY dbo.fn_getByID

(w.ID) AS r

ORDER BY w.ID;

======================================

GO

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

sanajmshaji (11/9/2011)
Hi Guys
Please refer the below link.
http://www.java2s.com/Tutorial/SQLServer/0080__Table-Join/UsingAPPLYtoInvokeaTableValuedFunctionforEachRow.htm
Step 1
---------------------------------------------------------------------
CREATE FUNCTION dbo.fn_getByID(@ID int) RETURNS TABLE
AS
RETURN SELECT first_name FROM employee WHERE ID = @ID
GO
---------------------------------------------------------------------
Step 2
SELECT w.last_name FROM employee w
CROSS APPLY dbo.fn_getByID
(w.ID) AS r
ORDER BY w.ID;
======================================
GO

That won't work in sql2000. There is no CROSS APPLY yet. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

fermin.js Valued Member Points: 57 More actions · Answer 12

Hi

This would also work

SELECT *

FROM dbo.CalculateIncome(p.personid);

Fermön

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

fermin.js (11/29/2011)
Hi
This would also work
SELECT *
FROM dbo.CalculateIncome(p.personid);
Fermön

I know this post is nearly a year old but ran across it just now. My question is, where does "p.personid" come from in that query?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

juan.wilke SSC Journeyman Points: 75 More actions · Answer 14

juan.wilke

SSC Journeyman

Points: 75

April 10, 2013 at 8:53 am

#1605272

Great post. Thank you!

It works like a dream......

Ken McCreery SSC Enthusiast Points: 157 More actions · Answer 15

I believe what you want is the CROSS APPLY function: https://technet.microsoft.com/en-US/library/ms175156(v=SQL.105).aspx