• To add to what the others have already stated...

    If you write a function as an iTVF or INLINE Table Valued Function, it will work just like a View except for one important factor... the iTVF will take parameters where a View cannot. That can work to a huge advantage if the result set is used as part of the join and the columns being joined on are calculated. A CTE would work in a similar fashion.

    The result sets of stored procedures can't be used in a FROM clause directly. You have to do a trick with OPENROWSET which has it's own privs and parameterization problems.

    That's just scratching the surface, though. As the others have suggested, you really need to hit the books on this question. One could write an entire book on the differences and when to use each for what.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)