CODEWITHSUNDEEP
sql-servert-sql
Dev Devon
Dev Devon

Reputation: 13

Table_valued function with Implicit output

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

Upvotes: 1

Views: 296

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5684

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).

Upvotes: 1

Related Questions