Using Table-Valued Function in Query

Posted on 2007-10-13
Last Modified: 2008-01-09
I have a table with the following fields (greatly simplified):

Item, Description, ID, Acc1, Acc2, Acc3, Acc4, Acc5, Acc6
Sample data

I am writing a procedure which takes this data and moves it into a properly normalised table, which will have the following fields:

Item, Description, ID, Office

The Office field value is determined by the values in Acc1, 2, 3 etc and so there may be between 1 and 6 records copied to the new table depending on the contents of these 6 fields.  I have written a table-valued function which examines these fields and returns a table with one field thus:


My question is:  How do I link this function into a select query so that I can get this result:

Item, Description, ID, Office

I'm looking for something like this (which obviously doesn't work):

SELECT O.Item, O.[Description], O.ID, N.Office
[dbo].[MYFUNCTION] (O.Acc1, O.Acc2, O.Acc3, O.Acc4) N
Question by:Spunkymungbeans
    1 Comment
    LVL 142

    Accepted Solution

    sql server 2005: discover the CROSS APPLY keyword:

    SELECT O.Item, O.[Description], O.ID, N.Office
    CROSS APPLY [dbo].[MYFUNCTION] (O.Acc1, O.Acc2, O.Acc3, O.Acc4) N

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now