1

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?

JeffO
  • 7,957
  • 3
  • 44
  • 53

1 Answers1

3

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I'm guessing the answer is no, but I've never seen this mentioned in any list that compares and contrasts procs and udf's. Good point on the dynamic sql. – JeffO Jul 29 '11 at 02:49
  • The actual limitation is not prohibiting dynamic SQL, per se, it's that you can't have side effects. This means you can't use EXEC (required for dynamic SQL, whether you use EXEC() by itself or EXEC sp_executeSQL). – Aaron Bertrand Jul 29 '11 at 03:11