How to return a table variable from a function (UDF)?

Question

I'm using SQL Server 2012 and I have been trying lots of different approaches to return a table variable from inside a function, but I cannot get it to work. I've tried moving the variable declaration to different places, etc. Here are the guts of the sql. If you could please wrap the guts in a UDF function that actually compiles and returns the @Financials table variable I would appreciate it. The sql works great, no problems. But when I try to wrap it in a UDF it throws errors when I try to create it. I hardcoded some stuff to make it easier to test and visualize.

DECLARE @Financials TABLE (
    [a bunch of variable declarations in here]
);

insert into @Financials
[big old SELECT query here - this all works fine, and populates @Financials]

    select * 
    from @Financials f1
    where f1.TransactionDate = (
        select MAX(TransactionDate)
        from @Financials
        where SalesDocumentItemID = f1.SalesDocumentItemID
    )

I need the UDF to return @Financials now.

If this is impossible, please consider my real problem, which is shown in the select * from @Financials above, in which I want to match only the latest TransactionDate, joined by SalesDocumentItemID. If I could find an efficient way to do this, I wouldn't need to do the INSERT into @Financials at all. I guess the problem is that the query that populates @Financials is complex, with lots of joins, and I don't want to duplicate all of it again in the subselect. But I'm guessing there's an awesome and easier way to do that. Would love some ideas.

Are you trying to make that function permanently available on the database? — Conduit
– Conduit, Commented Jun 26, 2014 at 17:24

Jerrad · Accepted Answer · 2014-06-26 20:37:37Z

7

You don't use DECLARE when returning a table variable. Define the result table in the RETURNS clause.

CREATE Function GetFinancials ()
RETURNS @financials TABLE
(
  [a bunch of variable declarations in here]
)
AS
BEGIN
    insert into @Financials
    [big old SELECT query here - this all works fine, and populates @Financials]

    RETURN
END

Update

How about returning the final result in a stored procedure?

create procedure uspGetFinanicals
as
  declare @financial table
  (
    [table definition here]
  )

  insert into @financial
  select dbo.GetFinancials()

  select * 
    from @Financials f1
    where f1.TransactionDate = (
        select MAX(TransactionDate)
        from @Financials
        where SalesDocumentItemID = f1.SalesDocumentItemID
    )

Update

Try this. Create a table variable within the UDF to store the results of the first select, then insert the result of the final query into the return value.

CREATE Function GetFinancials ()
RETURNS @financials TABLE
(
  [a bunch of variable declarations in here]
)
AS
BEGIN
    declare @table table([a bunch of variable declarations in here])
    insert into @table
    [big old SELECT query here - this all works fine, and populates @Financials]

    insert into @Financials
    select * 
      from @table f1
      where f1.TransactionDate = (
        select MAX(TransactionDate)
        from @table
        where SalesDocumentItemID = f1.SalesDocumentItemID
      )

    RETURN
END

edited Jun 26, 2014 at 20:37

answered Jun 26, 2014 at 17:35

Jerrad

5,3001 gold badge20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

HerrimanCoder Over a year ago

you've gotten me really close, but I'm getting 1 error now: "Select statements included within a function cannot return data to a client." It's complaining about that final SELECT statement with a subselect at the very end. How can I solve?

Conduit Over a year ago

@SweatCoder Correct - no selects allowed. To make the subset you need, you'll have to either restrict the data inserted into '@financials' or delete the extra data once it's inserted (the former being comparatively less expensive).

Nick H. Over a year ago

Hey Sweat, you can't return a table with a stored procedure.

Nick H. Over a year ago

This should be the solution. Nice Jerrad.

HerrimanCoder Over a year ago

It needs to be a function because many procs already do selects against it, like SELECT * FROM Financials(1,2,3). This Financials UDF already exists, I just need to change the sql inside as indicated, and that's what's not working.

|

Collectives™ on Stack Overflow

How to return a table variable from a function (UDF)?

1 Answer 1

Update

Update

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

Update

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related