如何从函数(UDF)返回表变量? [英] How to return a table variable from a function (UDF)?

问题描述

我正在使用 SQL Server 2012 并且我一直在尝试许多不同的方法来从函数内部返回表变量，但我无法让它工作.我试过将变量声明移动到不同的地方，等等.这里是 sql 的内容.如果您可以将胆量包装在实际编译并返回 @Financials 表变量的 UDF 函数中，我将不胜感激.sql 很好用，没有问题.但是当我尝试将它包装在 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
    )

我现在需要 UDF 来返回 @Financials.

I need the UDF to return @Financials now.

如果这是不可能的，请考虑我的真正问题，它显示在上面来自@Financials 的 select * 中，其中我只想匹配最新的 TransactionDate，由 SalesDocumentItemID 加入.如果我能找到一种有效的方法来做到这一点，我根本不需要对 @Financials 执行 INSERT.我想问题是填充@Financials 的查询很复杂，有很多连接，我不想在子选择中再次复制所有这些.但我猜有一种很棒且更简单的方法可以做到这一点.会喜欢一些想法.

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.

推荐答案

返回表变量时不要使用 DECLARE.在 RETURNS 子句中定义结果表.

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
    )

更新

试试这个.在UDF中创建一个表变量来存储第一次select的结果，然后将最终查询的结果插入到返回值中.

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

这篇关于如何从函数(UDF)返回表变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！