Dynamically return table with different columns in a User Defined Table Function.

There are several limitations between a SQL Server stored procedure and a user defined function. UDF's Can't

• use nondeterministic functions
• change the state of the database
• Return messages to the caller
• have any side effects

A stored procedure can return multiple record sets and they are not required to return the same fields each time.

create proc custom.sproc_CrazyFields
          @ThisItem int
as
 开发者_开发问答   begin
        if @ThisItem < 10
        begin
            select 'this' as ThisField, 'that' as ThatField, 'theOther' as theOtherField;
        end
        else
           begin
              Select 'theOther' as theOtherField, 'that' as thatField, 'this' as thisField;
           end
    end
    go
    exec custom.sproc_CrazyFields 4

    exec custom.sproc_CrazyFields 40

An inline function is only going to return a single select statement. A multistatement function has to declare the returned table.

Is there a way to dynamically return a result with changing columns with a UDF or is this one of the differences?

---

Sorry, you can't use dynamic SQL in a function. Maybe what you can do is write a stored procedure that creates a function in dynamic SQL, calls the function, then drops it. But then why not just build the query inline at that point.