Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to use table valued function as part of...
Add to briefcase

How to use table valued function as part of select statement in sql server Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
purushottam2
Posted Monday, February 04, 2013 11:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101
I want to use table valued function as part of select statement in sql server.
Below is example

INSERT INTO @Table (Id, Name, Salary, Leave)
SELECT x.Id, x.Name, udf_GetEmpDetail(x.Id) FROM Emp x

Here udf_GetEmpDetail table valued function will return two columns and 1 row table data.
Post #1415587
Jason-299789
Posted Tuesday, February 05, 2013 12:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 803, Visits: 2,123
As the TVF returns 2 columns you will have to do that as a using a CROSS APPLY

Something like this

SELECT x.Id, x.Name, y.Col1, y.Col2
FROM Emp x
CROSS APPLY udf_GetEmpDetail(x.Id) y

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1415593
purushottam2
Posted Tuesday, February 05, 2013 1:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101
Does CROSS APPLY will not produce duplicate result set in case employee table have duplicate id?
Post #1415628
Jason-299789
Posted Tuesday, February 05, 2013 1:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 803, Visits: 2,123
The problem you are facing is that the TVF returns 2 columns, thus you cannot put it in the Column List, so you must either use a JOIN or APPLY statement.

x.ID should be unique as its name indicates an Identity column, and as you have stated the TVF returns 1 row of data, this would be a 1-1 join thus you will only get one row back from the TVF for each of the rows in EMP.

But I dont have your data and the x.Id being unique is an assumption on my part.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1415633
purushottam2
Posted Tuesday, February 05, 2013 1:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101
Id columns i have just given for reference, in my case TVF will always return 1 row but in table may have multiple id. So i think it should not produce duplicate rows. Because there is Many X 1 relation.
Are you agree?
Post #1415635
demonfox
Posted Tuesday, February 05, 2013 1:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:46 PM
Points: 1,074, Visits: 1,076
purushottam2 (2/5/2013)
Id columns i have just given for reference, in my case TVF will always return 1 row but in table may have multiple id. So i think it should not produce duplicate rows. Because there is Many X 1 relation.
Are you agree?


could you provide ,sample data and the function code ?

Mutiple result set for a single column would throw an error ..

Edit :
It should not produce duplicate rows , if the function returns just one row.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1415640
Jason-299789
Posted Tuesday, February 05, 2013 1:40 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 803, Visits: 2,123
THe Cross apply will only return rows if the function you are calling returns rows that match x.Id. If you want to return all rows from EMP regardless of there being a row in the TVF then you will need to use an OUTER APPLY.

This means that if you have two rows in EMP that have the same ID then it will return multiple rows with the same data.

Eg

ID NAME
1 John
2 James
2 Richard

the cross apply (assuming the function returns a row for each ID) will return

1, John, somedata,somedata2
2, James, somedata,somedata2
2, Richard, somedata, somedata2

I would suggest reading this, its 2005 but stiull valid for 2008 and 2012 as far as im aware 
http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1415643
ashishjain
Posted Wednesday, February 06, 2013 11:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 12:10 AM
Points: 86, Visits: 9
It would be good if you can provide ,sample data and the function code.
Post #1416813
purushottam2
Posted Wednesday, February 06, 2013 11:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101
I have used CROSS APLLY, it worked fine for me. udf is having 1000+ line of codes and sp is also too much so not able to paste here and it will annoy you as well.
Post #1416816
Jason-299789
Posted Thursday, February 07, 2013 12:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 803, Visits: 2,123
purushottam2 (2/6/2013)
I have used CROSS APLLY, it worked fine for me. udf is having 1000+ line of codes and sp is also too much so not able to paste here and it will annoy you as well.


1000+ line UDF, what is it doing?

I must admit im very curious about what the UDF is doing in that number of lines of code, you could attatch the definition of the UDF as a text file, to save on the screen.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1416831
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.