Table_valued function with Implicit output

Question

I would like to build a very complicated Table_valued function (TVF) that will return non fixed output structure.

sometimes the TVF may return 2 columns and other times may return only 1 column.

I couldn't find a way to do this because the database engine requires explicit output table structure as:

RETURNS @returnTable 
TABLE 
(   
      column1 numeric,
      column2 numeric
)

Once i find a solution for the above i would like to do something like:

SELECT 
 *
INTO #tmp
FROM MyTVF

I know its possible to implement that with stored procedure but then i will face to another problem. By using stored procedure i will not be able to save the result to a temp table without declaring the output explicitly.

Here is a shot example of what i would like to do:

CREATE FUNCTION [dbo].myFunction (@type int)
RETURNS @table TABLE 
( 
    Column1 int,
    Column2 int

)
AS 
BEGIN


    IF @type=1 
    BEGIN
        INSERT INTO @table
        SELECT 1 AS Column1, 2 AS Column2
    END
    ELSE
    BEGIN
        INSERT INTO @table
        SELECT 1 AS OnlyOneColumn
    END

RETURN
END
GO

SELECT * INTO #tmp1 FROM myFunction(1)
SELECT * FROM #tmp1

You can declare a return table with the maximum number of columns. Then, when inserting, consume only the number of columns you are interested in. — Giorgos Betsos
– Giorgos Betsos, Commented Feb 8, 2015 at 15:07
@GiorgosBetsos, Do you suggest to always return the maximum number of columns and use NULL as value of the not relevant columns for the cenario? — Dev Devon
– Dev Devon, Commented Feb 8, 2015 at 15:10
Yes, use NULL to fill in the rendundant columns. It is just a workaround. You cannot have volatile return types in TVFs AFAIK, — Giorgos Betsos
– Giorgos Betsos, Commented Feb 8, 2015 at 15:14
If you declare the return table with all possible columns then you need to accommodate both the quantity of columns and the various different data types (including nullability?) that might be needed. And perhaps a bitmap to communicate which returned columns are valid since null is a perfectly good value for a column. — HABO
– HABO, Commented Feb 8, 2015 at 15:34

Razvan Socol · Accepted Answer · 2015-02-08 15:32:28Z

1

All Table-Valued Functions return a table with a fixed structure.

However (unlike Multi-Statement Table-Valued Functions), you don't have to declare that structure for an In-Line Table-Valued Function, for example:

CREATE FUNCTION MyFunction(@MyParameter INT)
RETURNS TABLE AS RETURN
SELECT * FROM SomeTable
WHERE SomeColumn=@MyParameter

This function will still return a fixed number of columns: even if you later add columns to SomeTable, they will not be returned by MyFunction, unless the function is modified or refreshed (with sp_refreshsqlmodule).

You cannot create a Table-Valued Function that returns a variable number of columns (depending on the input parameters).

answered Feb 8, 2015 at 15:32

Razvan Socol

5,7592 gold badges22 silver badges35 bronze badges

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

2 Comments

Dev Devon Over a year ago

Just tried to do something like this without success:

ALTER FUNCTION fn_testa(@MyParameter INT) RETURNS TABLE AS  BEGIN IF @MyParameter=1  BEGIN 	SELECT TOP 10 * FROM MyTable END ELSE BEGIN 	SELECT TOP 10 * FROM MyOtherTable END   GO SELECT * FROM fn_testa(1)

Can you please help?

Razvan Socol Over a year ago

The In-Line Table-Valued Functions work only with a single statement. If you want to use multiple statements, you will have a Multi-Statement Table-Valued Function, which requires you to declare the structure of the returned table.

Collectives™ on Stack Overflow

Table_valued function with Implicit output

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