Subscribe to
Posts
Comments

I know for a long time that table function can be used just like a physical table to participate in a join in SQL query. An example is like the following

select B.* from tableA as A inner join dbo.tablefunctionB() as B on A.id = B.id

In the above query dbo.tablefunctionB() is a table function that returns

a table with an id column. I encountered some cases before that the table function took a parameter and I had a list of values in another table that I wanted to pass to the table function as paramter. Assuming that the table function dbo.tablefunctionB takes a string (First name) as parameter and tableA is a table that holds all the first names that I want to pass to the table function. So I tried the following query

select B.* from tableA as A inner join dbo.tablefunctionB(A.FristName) as B on A.id = B.id

It will fail and SQL server will tell you there is syntex error in the query. You can only pass a value or variable to the table function as parameter and you can not pass a column as the paramter. I was thinking at that time that it would be very nice if SQL server allow us do this. Now with SQL Server 2005 we can do this with cross apply or outer apply operators. You just need to change the inner join to cross apply and move the join condition to a where statement like this.

select B.* from tableA as A cross apply dbo.tablefunctionB(A.FirstName) as B where A.id = B.id

For left outer join you may use “outer apply” operator.


Related Posts:

  • List All Permissions a User Has in SQL Server Database and Error 4064
  • New Answer on How to Defragment a Table Being Used 24/7
  • How to Script Out all The Foreign Keys of a Table
  • Find the First Occurrence of a Pattern Using Regular Expression in T-SQL
  • A Fast Way to Update Large Amount of Data in SQL Server With Select Into Statement


  • 1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 4.33 out of 5)
    Loading ... Loading ...

    RSS feed | Trackback URI

    15 Comments »

    Comment by andy hendawan
    2008-07-27 20:39:00
    MyAvatars 0.2

    still not working
    my command is

    select *
    from SLSINVOICEdt as dt
    inner join SLSINVOICEHD as hd on dt.invoiceno=hd.INVOICENO
    inner join MSCUST as cust on cust.CUSTCODE=hd.CUSTCODE
    cross apply F_GATTAX(hd.CUSTCODE,hd.INVOICENO,cust.TAXFLAG) as tot
    where tot.invoiceno=hd.INVOICENO

    and i still got error “Incorrect syntax near ‘.’”

    thanks

    Comment by Catalin Hatmanu
    2008-08-15 09:27:48
    MyAvatars 0.2

    I had same error as you “Incorrect syntax near ‘.’” and after changing the database compatibility level to SQL Server 2005(take a look at Database Properties ) the query worked fine.

     
     
    Comment by Andrew Chen
    2008-08-02 17:11:04
    MyAvatars 0.2

    It looks like an error within your function F_GATTAX

     
    Comment by paul
    2009-04-30 01:07:55
    MyAvatars 0.2

    just what i wanted cheers! :smile:

     
    Comment by Guilherme
    2009-04-30 06:59:44
    MyAvatars 0.2

    Very nice post !!!
    Thank U.

     
    Comment by Yeya
    2009-06-02 12:50:50
    MyAvatars 0.2

    Thanks a lot, just what i needed. :razz:

     
    Comment by neb12pgmr
    2009-07-06 12:25:51
    MyAvatars 0.2

    Excellent post!!! Thanks for your insight!

     
    Comment by Morgan
    2009-08-19 08:22:03
    MyAvatars 0.2

    Thank you so much for this. Was banging my head against the wall with “multi-part identifier not found” error.

     
    Comment by pyay
    2010-01-05 03:56:50
    MyAvatars 0.2

    Thanks a lot for the nice post..it definitely help me to solve the problem.

     
    Comment by Josue Monteiro Viana
    2010-09-13 11:55:19
    MyAvatars 0.2

    Hello people,

    I tried to use the examples and got in trouble because “cross apply” only works on SQL 90 compatibility mode – not in SQL 2005 with mode 80 enabled (my case). Since I can’t change the database, I create the following work around:
    – Code
    USE tempdb – tempdb is a native SQL 2005 DB
    SELECT
    tb.field1,
    tb.field2,
    fc.*
    FROM #tempTB as tb
    CROSS APPLY OficialDB_Compatibility80.dbo.myUDF (tb.field1, tb.field2, tb.field3, @dinamicVAR) AS fc
    USE OficialDB_Compatibility80
    – Code

    This worked for me. Good lock!

    Bye
    Josué Monteiro Viana

     
    Comment by Fox
    2010-09-16 01:58:19
    MyAvatars 0.2

    Excellent.
    Thanks!

     
    Comment by DanO
    2011-04-13 05:16:40
    MyAvatars 0.2

    Nice one. This has saved me an awful lot of time. :mrgreen:

     
    Comment by jpaquete
    2011-05-20 03:13:14
    MyAvatars 0.2

    thanks man :)

     
    Comment by daDude Subscribed to comments via email
    2011-07-26 06:05:35
    MyAvatars 0.2

    I wonder why this following doesn’t work in sql-server2008, then?

    select * From sys.databases AS d CROSS APPLY sys.dm_io_virtual_file_stats(d.database_id, null) as fn

    an ideas?

     
    Comment by daDude Subscribed to comments via email
    2011-07-26 06:18:55
    MyAvatars 0.2

    Just an FYI:

    It turns out the function to which we’re cross applying cannot be an inline function… it has to return a table with… it’s a known bug apparently:
    http://connect.microsoft.com/SQLServer/feedback/details/241325/cross-apply-and-dynamic-management-functions

    anyways, your post definitely put me in the right direction.

    cheers!

     
    Name (required)
    E-mail (required - never shown publicly)
    URI
    Subscribe to comments via email
    Your Comment (smaller size | larger size)
    You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.