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 (3 votes, average: 5 out of 5)
    Loading ... Loading ...

    RSS feed | Trackback URI

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

     
    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.