Multi-statement table-valued functions

Emulating table-valued functions with more than one statement can get messy. We use a chain of CTEs, grouping compatible statements to end up with the simplest possible output.

---

Code examples

SQL Server:

CREATE FUNCTION dbo.ExampleTVF_InsertSkipNull()
 RETURNS @Result TABLE
 (
     Value NVARCHAR(50) NULL,
     Extra NVARCHAR(50) NULL
 )
 AS
 BEGIN
     INSERT INTO @Result (Value, Extra)
     VALUES ('First', 'X');
 
     INSERT INTO @Result (Value)
     VALUES ('Second');
 
     INSERT INTO @Result (Extra)
     VALUES ('Y');
 
     RETURN;
 END

Fabric Warehouse (generated by SQL Tran):

CREATE FUNCTION dbo.ExampleTVF_InsertSkipNull()
RETURNS TABLE
AS
RETURN
	SELECT *
	FROM
		(SELECT 'First' AS Value, 'X' AS Extra
		UNION ALL
		SELECT 'Second' AS Value, NULL AS Extra
		UNION ALL
		SELECT NULL AS Value, 'Y' AS Extra) AS subQuery;

---

SQL Server:

CREATE FUNCTION dbo.simpleVariableDependenceCondition()
 RETURNS @Result TABLE
 (
    Id INT,
    Value INT
 )
 AS
 BEGIN
    DECLARE @a INT = 2;
    DECLARE @b INT;
    DECLARE @d INT;

    SET @b = @a * 10;

    IF (@b % 2 = 0)
      SET @d = @a + @b;  
     ELSE
      SET @d = @a - @b;

    INSERT INTO @Result
        (Id, Value)
    VALUES
        (1, @a),
        (2, @b),
        (3, @d);

    RETURN;
END;

Fabric Warehouse (generated by SQL Tran):

CREATE FUNCTION dbo.simpleVariableDependenceCondition()
RETURNS TABLE
AS
RETURN
	WITH
		group_1 AS
		(SELECT 2 AS a_),
		group_2 AS
		(SELECT (SELECT a_
			FROM
				group_1) * 10 AS b_),
		group_3 AS
		(SELECT CASE
				WHEN NOT (((SELECT b_
				FROM
					group_2) % 2 = 0)) THEN (SELECT a_
				FROM
					group_1) - (SELECT b_
				FROM
					group_2)
				WHEN ((SELECT b_
				FROM
					group_2) % 2 = 0) THEN (SELECT a_
				FROM
					group_1) + (SELECT b_
				FROM
					group_2)
			END  AS d_)
	SELECT *
	FROM
		(SELECT 1 AS Id,
			(SELECT a_
			FROM
				group_1) AS Value
		UNION ALL
		SELECT 2 AS Id,
			(SELECT b_
			FROM
				group_2) AS Value
		UNION ALL
		SELECT 3 AS Id,
			(SELECT d_
			FROM
				group_3) AS Value) AS subQuery;