TSQL UDF return table with different number of columns depending on value of first function parameter

Question

Is it possible? If not, what are other options?

One of the things I tried to do so far is this (doesn't work; I'm also aware that in case that first argument's value is < 0 or > 4 I'm trying to return an uninitialized variable, but this is approximately what I'd aim to get):


SET NOCOUNT ON;
USE AdventureWorks2008;
GO

IF OBJECT_ID('dbo.fn_GetPopleInfo') IS NOT NULL
 DROP FUNCTION dbo.fn_GetPopleInfo;
GO

CREATE FUNCTION dbo.fn_GetPopleInfo
 (@columns INT) RETURNS TABLE
AS
BEGIN
    IF (@columns = 1)
        CREATE TABLE @People ( LastName AS VARCHAR(50) );
        INSERT INTO @People SELECT P.LastName
                            FROM Person.Person AS P
    ELSE IF (@columns = 2)
        CREATE TABLE @People ( LastName AS VARCHAR(50), FirstName AS VARCHAR(50) );
        INSERT INTO @People SELECT P.LastName, Person.FirstName
                            FROM Person.Person AS P
    ELSE IF (@columns = 3)
        CREATE TABLE @People ( LastName AS VARCHAR(50), FirstName AS VARCHAR(50), Email AS VARCHAR(50) );
        INSERT INTO @People SELECT P.LastName, P.FirstName, E.EmailAddress
                            FROM Person.Person AS P
                            INNER JOIN Person. EmailAddress AS E ON P.BusinessEntityID = E.BusinessEntityID
    ELSE IF (@columns = 4)
        CREATE TABLE @People ( LastName AS VARCHAR(50), FirstName AS VARCHAR(50), Email AS VARCHAR(50), Address AS VARCHAR(50) );
        INSERT INTO @People SELECT P.LastName, P.FirstName, E.EmailAddress, A.AddressLine1
                            FROM Person.Person AS P
                            INNER JOIN Person.EmailAddress AS E ON P.BusinessEntityID = E.BusinessEntityID
                            INNER JOIN Person.Address AS A ON E.AddressID = A.AddressID
    RETURN @People
END

Another option I can use is returning a scalar value with concatenated info, like this (but I'd prefer much more to get a table):

SET NOCOUNT ON;
USE AdventureWorks2008;
GO

IF OBJECT_ID('dbo.fn_ConcatPeopleData') IS NOT NULL
 DROP FUNCTION dbo.fn_ConcatPeopleData;
GO


CREATE FUNCTION dbo.fn_ConcatPeopleData
 (@bid AS NCHAR(5), @columns AS INT ) RETURNS NCHAR(1000)
AS
BEGIN
DECLARE @data AS VARCHAR(8000);
SET @data = '';
    IF (@columns = 1)
        SELECT @data = @data + CAST(LastName AS VARCHAR(100))
        FROM Person.Person AS P
        WHERE P.BusinessEntityID = @bid;

    ELSE IF (@columns = 2)
        SELECT @data = @data + CAST(LastName AS VARCHAR(100))+' ' + CAST(FirstName AS VARCHAR(100))
        FROM Person.Person AS P
        WHERE P.BusinessEntityID = @bid;

    ELSE IF (@columns = 3)
         SELECT @data = @data + CAST(LastName AS VARCHAR(100))+' ' + CAST(FirstName AS VARCHAR(100))+', ' +
                               CAST(E.EmailAddress AS VARCHAR(100))
         FROM Person.Person AS P 
         INNER JOIN Person. EmailAddress AS E ON P.BusinessEntityID = E.BusinessEntityID
         WHERE P.BusinessEntityID = @bid;

    ELSE IF (@columns = 4)
        SELECT @data = @data + CAST(LastName AS VARCHAR(100))+' ' + CAST(FirstName AS VARCHAR(100))+', ' + 
                               CAST(E.EmailAddress AS VARCHAR(100))+', ' + CAST(A.AddressLine1 AS VARCHAR(100))+' ' +
                               CAST(A.City AS VARCHAR(100))+' ' + CAST(A.PostalCode AS VARCHAR(100))
               FROM Person.Person AS P
               INNER JOIN Person.EmailAddress AS E ON P.BusinessEntityID = E.BusinessEntityID
               INNER JOIN Person.BusinessEntityAddress AS BA ON P.BusinessEntityID = BA.BusinessEntityID
               INNER JOIN Person.Address AS A ON BA.AddressID = A.AddressID
               WHERE P.BusinessEntityID = @bid;
RETURN @data;
END


SELECT BusinessEntityID, dbo.fn_ConcatPeopleData(BusinessEntityID, 3) AS [Person Info]
FROM Person.Person;

Not sure of you usage but you'll need a stored procedure rather than a function. That said, a varying result set schema that depends on the provided parameter value suggests a flawed interface. — Dan Guzman, Mar 21 '20 at 14:32
@DanGuzman Totally agree, sounds like XY problem. Few ideas to get specific output: 1) JSON and parse it outside func/2) temp table and drop unused column(hackish) 3) [Polymorphic functions](https://stackoverflow.com/a/49015504/5070879) - not available for SQL Server — Lukasz Szozda, Mar 21 '20 at 14:33

score 0 · Answer 1 · answered Mar 21 '20 at 14:32

Actually, if you define the return value, your code would work:

DECLARE @People TABLE (
    Lastname VARCHAR(50),
    FirstName VARCHAR(50),
    Email VARCHAR(50),
    Address VARCHAR(50)
) ;

IF @columns = 1 BEGIN
    INSERT INTO @People (LastName)
        SELECT P.LastName
        FROM Person.Person AS P;
END;
ELSE IF @columns = 2 BEGIN
    INSERT INTO @People (LastName, FirstName)
        SELECT P.LastName, P.FirstName
        FROM Person.Person P;
END;
. . . 
RETURN @People

The unused columns will be NULL, which is a perfectly reasonable value for columns that don't have an assigned value.

Notes:

Temporary tables do not being with @. They begin with #. In this case, a table variable is just fine.
I am not aware that as can be used when declaring the type of a column in a table. It is used for generated columns.
Be careful about table aliases.

TSQL UDF return table with different number of columns depending on value of first function parameter

1 Answers1