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

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

i'm using sql server 2012 , have been trying lots of different approaches homecoming table variable within function, cannot work. i've tried moving variable declaration different places, etc. here guts of sql. if please wrap guts in udf function compiles , returns @financials table variable appreciate it. sql works great, no problems. when seek wrap in udf throws errors when seek create it. hardcoded stuff create easier test , visualize.

declare @financials table ( [a bunch of variable declarations in here] ); insert @financials [big old select query here - works fine, , populates @financials] select * @financials f1 f1.transactiondate = ( select max(transactiondate) @financials salesdocumentitemid = f1.salesdocumentitemid )

i need udf homecoming @financials now.

if impossible, please consider real problem, shown in select * @financials above, in want match latest transactiondate, joined salesdocumentitemid. if find efficient way this, wouldn't need insert @financials @ all. guess problem query populates @financials complex, lots of joins, , don't want duplicate of 1 time again in subselect. i'm guessing there's awesome , easier way that. love ideas.

you don't utilize declare when returning table variable. define result table in returns clause.

create function getfinancials () returns @financials table ( [a bunch of variable declarations in here] ) begin insert @financials [big old select query here - works fine, , populates @financials] homecoming end update

how returning final result in stored procedure?

create procedure uspgetfinanicals declare @financial table ( [table definition here] ) insert @financial select dbo.getfinancials() select * @financials f1 f1.transactiondate = ( select max(transactiondate) @financials salesdocumentitemid = f1.salesdocumentitemid ) update

try this. create table variable within udf store results of first select, insert result of final query homecoming value.

create function getfinancials () returns @financials table ( [a bunch of variable declarations in here] ) begin declare @table table([a bunch of variable declarations in here]) insert @table [big old select query here - works fine, , populates @financials] insert @financials select * @table f1 f1.transactiondate = ( select max(transactiondate) @table salesdocumentitemid = f1.salesdocumentitemid ) homecoming end

sql-server tsql sql-server-2012