1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eric Ma
  • 91
  • 1
  • 5
  • 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 Jun 15 '18 at 23:28
  • Should probably be `RETURNS TABLE`? Is [this](http://sqlhints.com/tag/multi-statement-table-valued-user-defined-function/) relevant? Not to sure about using an undefined tabletype as parameter. – LukStorms Jun 15 '18 at 23:35

1 Answers1

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
UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51