Input and output a table from a function in SQL

Question

I would like to create a function in T-SQL which both inputs and outputs instances of a user-defined table type. However, on the line of the RETURN statement, it is throwing an error:

Must declare the scalar variable @output_table

My code:

CREATE FUNCTION FUNC_NAME
     (@input_table TableType READONLY, 
      @other_param VARCHAR(255))
RETURNS TableType
AS
BEGIN
    DECLARE @output_table TableType;

    INSERT INTO @output_table (Col1, Col2, Col3)
        SELECT column1, column2, column3
        FROM SOME_TABLE
        WHERE column1 = @other_param;

    RETURN @output_table;
END

Can someone please explain to me what I am doing wrong? Is it even possible to create a function that both inputs and outputs a table type variable? If not, are there any alternative approaches that I may use? I am using SQL Server Management Studio 2008 R2.

Thanks. However, i just tried that - now it's giving me another error: "Return statements in scalar valued functions must include an argument." I'm confused, because this function returns a table type, but it's not a table valued function? — Eric Ma
– Eric Ma, Commented Jun 15, 2018 at 23:28
Should probably be RETURNS TABLE? Is this relevant? Not to sure about using an undefined tabletype as parameter. — LukStorms
– LukStorms, Commented Jun 15, 2018 at 23:35

UnhandledExcepSean · Accepted Answer · 2018-06-16 01:36:59Z

2

You don't currently or least I don't know how and can't find an example. It kind of makes sense because when you return a table/dataset, it doesn't need to be a specific table type since it is a generic data set.

Here is how it would be done:

CREATE TYPE IntTableType AS TABLE (ID INT);  
GO  


CREATE FUNCTION FUNC_NAME(@input_table IntTableType readonly)
RETURNS @output_table TABLE(ID VARCHAR(20))
AS
BEGIN
    INSERT INTO @output_table (ID)
        SELECT CONVERT(VARCHAR(20),ID)
        FROM @input_table

    RETURN;
END
GO

answered Jun 16, 2018 at 1:36

UnhandledExcepSean

12.9k4 gold badges38 silver badges54 bronze badges

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

2 Comments

David Dubois Over a year ago

A table variable can be passed to Func_Name. Is there any way to pass a database table as a parameter?

UnhandledExcepSean Over a year ago

@DavidDubois yes, look up dynamic sql table parameter and you are sure to find a solution

Collectives™ on Stack Overflow

Input and output a table from a function in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related