How to use table valued function as part of select statement in sql server

  • 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.

  • 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

  • Does CROSS APPLY will not produce duplicate result set in case employee table have duplicate id?

  • 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

  • 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?

  • 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:

  • 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

  • It would be good if you can provide ,sample data and the function code.

  • 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.

  • 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