February 4, 2013 at 11:59 pm
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.
February 5, 2013 at 12:11 am
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
February 5, 2013 at 1:11 am
Does CROSS APPLY will not produce duplicate result set in case employee table have duplicate id?
February 5, 2013 at 1:23 am
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
February 5, 2013 at 1:28 am
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?
February 5, 2013 at 1:32 am
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 :ermm:
February 5, 2013 at 1:40 am
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
February 6, 2013 at 11:20 pm
It would be good if you can provide ,sample data and the function code.
February 6, 2013 at 11:28 pm
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.
February 7, 2013 at 12:11 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply