CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
HerrimanCoder
HerrimanCoder

Reputation: 7218

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

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.

Upvotes: 6

Views: 10479

Answers (1)

Jerrad
Jerrad

Reputation: 5290

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

Upvotes: 7

Related Questions