SQL what is wrong with the syntax here?

Question

What is wrong with the syntax in this function? I am getting the following errors:

        ALTER FUNCTION [dbo].[udf_ReportingLevelStructure2] 
(   
    @CompanyID INT
)
RETURNS @result TABLE 
( 
CompanyName VARCHAR(300), 
[rl_Index] INT, 
[rl_Addr1] VARCHAR(MAX), 
[rl_Addr2] VARCHAR(MAX), 
[rl_DisplayName] VARCHAR(MAX) 
)
AS  
BEGIN 


DECLARE @tmp1 TABLE
(
CompanyName VARCHAR(300),
[rl_Index] INT,
[rl_Addr1] VARCHAR(MAX),
[rl_Addr2] VARCHAR(MAX),
[rl_DisplayName] VARCHAR(MAX)
);
DECLARE @tmp2 TABLE
(
[CompanyName] VARCHAR(300),
[rl_Index] INT,
[rl_Addr1] VARCHAR(MAX),
[rl_Addr2] VARCHAR(MAX),
[rl_DisplayName] VARCHAR(MAX)
)

INSERT INTO @tmp1([CompanyName],[rl_Index],[rl_Addr1],[rl_Addr2],[rl_DisplayName])
(SELECT
        rl0.[rl_Name] AS [CompanyName] 
        ,rl_Index =
        CASE
            WHEN rl9.[rl_Name] IS NOT NULL THEN rl9.[rl_Index]
            WHEN rl8.[rl_Name] IS NOT NULL THEN rl8.[rl_Index]
            WHEN rl7.[rl_Name] IS NOT NULL THEN rl7.[rl_Index]
            WHEN rl6.[rl_Name] IS NOT NULL THEN rl6.[rl_Index]
            WHEN rl5.[rl_Name] IS NOT NULL THEN rl5.[rl_Index]
            WHEN rl4.[rl_Name] IS NOT NULL THEN rl4.[rl_Index]
            WHEN rl3.[rl_Name] IS NOT NULL THEN rl3.[rl_Index]
            WHEN rl2.[rl_Name] IS NOT NULL THEN rl2.[rl_Index]
            WHEN rl1.[rl_Name] IS NOT NULL THEN rl1.[rl_Index]
        END 
        ,rl_Addr1 =
        CASE
            WHEN rl9.[rl_Name] IS NOT NULL THEN rl9.[rl_Addr1]
            WHEN rl8.[rl_Name] IS NOT NULL THEN rl8.[rl_Addr1]
            WHEN rl7.[rl_Name] IS NOT NULL THEN rl7.[rl_Addr1]
            WHEN rl6.[rl_Name] IS NOT NULL THEN rl6.[rl_Addr1]
            WHEN rl5.[rl_Name] IS NOT NULL THEN rl5.[rl_Addr1]
            WHEN rl4.[rl_Name]IS NOT NULL THEN rl4.[rl_Addr1]
            WHEN rl3.[rl_Name] IS NOT NULL THEN rl3.[rl_Addr1]
            WHEN rl2.[rl_Name] IS NOT NULL THEN rl2.[rl_Addr1]
            WHEN rl1.[rl_Name] IS NOT NULL THEN rl1.[rl_Addr1]
        END
        ,rl_Addr2 =
        CASE
            WHEN rl9.[rl_Name] IS NOT NULL THEN rl9.[rl_Addr2]
            WHEN rl8.[rl_Name] IS NOT NULL THEN rl8.[rl_Addr2]
            WHEN rl7.[rl_Name] IS NOT NULL THEN rl7.[rl_Addr2]
            WHEN rl6.[rl_Name] IS NOT NULL THEN rl6.[rl_Addr2]
            WHEN rl5.[rl_Name] IS NOT NULL THEN rl5.[rl_Addr2]
            WHEN rl4.[rl_Name] IS NOT NULL THEN rl4.[rl_Addr2]
            WHEN rl3.[rl_Name] IS NOT NULL THEN rl3.[rl_Addr2]
            WHEN rl2.[rl_Name] IS NOT NULL THEN rl2.[rl_Addr2]
            WHEN rl1.[rl_Name]IS NOT NULL THEN rl1.[rl_Addr2]
        END
        ,CASE
            WHEN rl9.[rl_Name] IS NOT NULL THEN rl1.[rl_Name]+'> '+rl2.[rl_Name]+'> '+rl3.[rl_Name]+'> '+rl4.[rl_Name]+'> '+ rl5.[rl_Name]+'> '+ rl6.[rl_Name]+'> '+rl7.[rl_Name]+'> '+ rl8.[rl_Name]+'> '+ rl9.[rl_Name]
            WHEN rl8.[rl_Name] IS NOT NULL THEN rl1.[rl_Name]+'> '+rl2.[rl_Name]+'> '+rl3.[rl_Name]+'> '+rl4.[rl_Name]+'> '+ rl5.[rl_Name]+'> '+ rl6.[rl_Name]+'> '+rl7.[rl_Name]+'> '+ rl8.[rl_Name]
            WHEN rl7.[rl_Name] IS NOT NULL THEN rl1.[rl_Name]+'> '+rl2.[rl_Name]+'> '+rl3.[rl_Name]+'> '+rl4.[rl_Name]+'> '+ rl5.[rl_Name]+'> '+ rl6.[rl_Name]+'> '+rl7.[rl_Name]
            WHEN rl6.[rl_Name] IS NOT NULL THEN rl1.[rl_Name]+'> '+rl2.[rl_Name]+'> '+rl3.[rl_Name]+'> '+rl4.[rl_Name]+'> '+ rl5.[rl_Name]+'> '+ rl6.[rl_Name]
            WHEN rl5.[rl_Name] IS NOT NULL THEN rl1.[rl_Name]+'> '+rl2.[rl_Name]+'> '+rl3.[rl_Name]+'> '+rl4.[rl_Name]+'> '+ rl5.[rl_Name]
            WHEN rl4.[rl_Name] IS NOT NULL THEN rl1.[rl_Name]+'> '+rl2.[rl_Name]+'> '+rl3.[rl_Name]+'> '+rl4.[rl_Name]
            WHEN rl3.[rl_Name] IS NOT NULL THEN rl1.[rl_Name]+'> '+rl2.[rl_Name]+'> '+rl3.[rl_Name]
            WHEN rl2.[rl_Name] IS NOT NULL THEN rl1.[rl_Name]+'> '+rl2.[rl_Name] 
            WHEN rl1.[rl_Name] IS NOT NULL THEN rl1.[rl_Name] 
        END AS [rl_DisplayName]

    FROM
        dbo.[ReportingLevels] rl0

        LEFT JOIN ReportingLevels rl1
            ON (rl1.[rl_CompanyID] = @CompanyID
            AND rl1.[rl_Indent1] <> 0 
            AND rl1.[rl_Indent2] = 0)

        LEFT JOIN ReportingLevels rl2
            ON (rl2.[rl_CompanyID] = @CompanyID
            AND rl2.[rl_Indent1] = rl1.[rl_Indent1]
            AND rl2.[rl_Indent2] <> 0
            AND rl2.[rl_Indent3] = 0)

        LEFT JOIN ReportingLevels rl3
            ON (rl3.[rl_CompanyID] = @CompanyID
            AND rl3.[rl_Indent2] = rl2.[rl_Indent2]
            AND rl3.[rl_Indent1] = rl2.[rl_Indent1]
            AND rl3.[rl_Indent3] <> 0
            AND rl3.[rl_Indent4] = 0)

        LEFT JOIN ReportingLevels rl4
            ON (rl4.[rl_CompanyID] = @CompanyID
            AND rl4.[rl_Indent3]= rl3.[rl_Indent3]
            AND rl4.[rl_Indent2] = rl3.[rl_Indent2]
            AND rl4.[rl_Indent1] = rl3.[rl_Indent1]
            AND rl4.[rl_Indent4] <> 0
            AND rl4.[rl_Indent5] = 0)

        LEFT JOIN ReportingLevels rl5
            ON (rl5.[rl_CompanyID] = @CompanyID
            AND rl5.[rl_Indent4] = rl4.[rl_Indent4]
            AND rl5.[rl_Indent3] = rl4.[rl_Indent3]
            AND rl5.[rl_Indent2] = rl4.[rl_Indent2]
            AND rl5.[rl_Indent1] = rl4.[rl_Indent1]
            AND rl5.[rl_Indent5] <> 0
            AND rl5.[rl_Indent6]= 0)

        LEFT JOIN ReportingLevels rl6
            ON (rl6.[rl_CompanyID] = @CompanyID
            AND rl6.[rl_Indent5] = rl5.rl_Indent5
            AND rl6.[rl_Indent4] = rl5.rl_Indent4
            AND rl6.[rl_Indent3] = rl5.rl_Indent3
            AND rl6.[rl_Indent2] = rl5.rl_Indent2
            AND rl6.[rl_Indent1] = rl5.rl_Indent1 
            AND rl6.[rl_Indent6] <> 0
            AND rl6.[rl_Indent7] = 0)

        LEFT JOIN ReportingLevels rl7
            ON (rl7.[rl_CompanyID] = @CompanyID
            AND rl7.[rl_Indent6] = rl6.[rl_Indent6]
            AND rl7.[rl_Indent5] = rl6.[rl_Indent5]
            AND rl7.[rl_Indent4] = rl6.[rl_Indent4]
            AND rl7.[rl_Indent3] = rl6.[rl_Indent3]
            AND rl7.[rl_Indent2] = rl6.[rl_Indent2]
            AND rl7.[rl_Indent1] = rl6.[rl_Indent1] 
            AND rl7.[rl_Indent7] <> 0
            AND rl7.[rl_Indent8] = 0)           

        LEFT JOIN ReportingLevels rl8
            ON (rl8.[rl_CompanyID] = @CompanyID
            AND rl8.[rl_Indent7] = rl7.[rl_Indent7]
            AND rl8.[rl_Indent6] = rl7.[rl_Indent6]
            AND rl8.[rl_Indent5] = rl7.[rl_Indent5]
            AND rl8.[rl_Indent4] = rl7.[rl_Indent4]
            AND rl8.[rl_Indent3] = rl7.[rl_Indent3]
            AND rl8.[rl_Indent2] = rl7.[rl_Indent2]
            AND rl8.[rl_Indent1] = rl7.[rl_Indent1] 
            AND rl8.[rl_Indent8]<> 0
            AND rl7.[rl_Indent9] = 0)

        LEFT JOIN ReportingLevels rl9
            ON (rl9.[rl_CompanyID] = @CompanyID
            AND rl9.[rl_Indent8] = rl8.[rl_Indent8]
            AND rl9.[rl_Indent7] = rl8.[rl_Indent7]
            AND rl9.[rl_Indent6] = rl8.[rl_Indent6]
            AND rl9.[rl_Indent5] = rl8.[rl_Indent5]
            AND rl9.[rl_Indent4] = rl8.[rl_Indent4]
            AND rl9.[rl_Indent3] = rl8.[rl_Indent3]
            AND rl9.[rl_Indent2] = rl8.[rl_Indent2]
            AND rl9.[rl_Indent1] = rl8.[rl_Indent1] 
            AND rl9.[rl_Indent9] <> 0
            AND rl9.[rl_Indent10] = 0)

    WHERE
        rl0.[rl_CompanyID] = @CompanyID
        AND rl0.[rl_Indent1] = 0);

INSERT INTO @tmp2([CompanyName],[rl_Index],[rl_Addr1],[rl_Addr2],[rl_DisplayName])
(SELECT DISTINCT
 rl0.[rl_Name] AS [CompanyName] 
,rl1.[rl_Index]
,rl1.[rl_Addr1]
,rl1.[rl_Addr2]
,CASE WHEN (rl1.[rl_indent1] <> 0 AND rl1.[rl_indent2] = 0 AND rl1.[rl_indent3] = 0 AND rl1.[rl_indent4] = 0 AND rl1.[rl_indent5] = 0 AND rl1.[rl_indent6] = 0 AND rl1.[rl_indent7] = 0 AND rl1.[rl_indent8] = 0 AND rl1.[rl_indent9] = 0) THEN rl1.[rl_Name] END AS [rl_DisplayName]

FROM
        [dbo].[ReportingLevels] rl0

        LEFT JOIN ReportingLevels rl1
            ON (rl1.[rl_CompanyID] = @CompanyID
            AND rl1.[rl_Indent1] <> 0 
            AND rl1.[rl_Indent2] = 0)

        LEFT JOIN ReportingLevels rl2
            ON (rl2.[rl_CompanyID] = @CompanyID
            AND rl2.[rl_Indent1] = rl1.[rl_Indent1] 
            AND rl2.[rl_Indent2] <> 0
            AND rl2.[rl_Indent3] = 0)

        LEFT JOIN ReportingLevels rl3
            ON (rl3.[rl_CompanyID] = @CompanyID
            AND rl3.[rl_Indent2] = rl2.[rl_Indent2]
            AND rl3.[rl_Indent1] = rl2.[rl_Indent1]
            AND rl3.[rl_Indent3] <> 0
            AND rl3.[rl_Indent4] = 0)

        LEFT JOIN ReportingLevels rl4
            ON (rl4.[rl_CompanyID] = @CompanyID
            AND rl4.[rl_Indent3] = rl3.[rl_Indent3]
            AND rl4.[rl_Indent2] = rl3.[rl_Indent2]
            AND rl4.[rl_Indent1] = rl3.[rl_Indent1] 
            AND rl4.[rl_Indent4] <> 0
            AND rl4.[rl_Indent5] = 0)

        LEFT JOIN [ReportingLevels] rl5
            ON (rl5.[rl_CompanyID] = @CompanyID
            AND rl5.[rl_Indent4] = rl4.[rl_Indent4]
            AND rl5.[rl_Indent3] = rl4.[rl_Indent3]
            AND rl5.[rl_Indent2] = rl4.[rl_Indent2]
            AND rl5.[rl_Indent1] = rl4.[rl_Indent1] 
            AND rl5.[rl_Indent5] <> 0
            AND rl5.[rl_Indent6] = 0)

        LEFT JOIN ReportingLevels rl6
            ON (rl6.[rl_CompanyID] = @CompanyID
            AND rl6.[rl_Indent5] = rl5.[rl_Indent5]
            AND rl6.[rl_Indent4] = rl5.[rl_Indent4]
            AND rl6.[rl_Indent3] = rl5.[rl_Indent3]
            AND rl6.[rl_Indent2]= rl5.[rl_Indent2]
            AND rl6.[rl_Indent1] = rl5.[rl_Indent1] 
            AND rl6.[rl_Indent6] <> 0
            AND rl6.[rl_Indent7] = 0)

        LEFT JOIN ReportingLevels rl7
            ON (rl7.[rl_CompanyID] = @CompanyID
            AND rl7.[rl_Indent6] = rl6.[rl_Indent6]
            AND rl7.[rl_Indent5] = rl6.[rl_Indent5]
            AND rl7.[rl_Indent4] = rl6.[rl_Indent4]
            AND rl7.[rl_Indent3] = rl6.[rl_Indent3]
            AND rl7.[rl_Indent2] = rl6.[rl_Indent2]
            AND rl7.[rl_Indent1] = rl6.[rl_Indent1] 
            AND rl7.[rl_Indent7] <> 0
            AND rl7.[rl_Indent8] = 0)           

        LEFT JOIN ReportingLevels rl8
            ON (rl8.[rl_CompanyID] = @CompanyID
            AND rl8.[rl_Indent7] = rl7.[rl_Indent7]
            AND rl8.[rl_Indent6] = rl7.[rl_Indent6]
            AND rl8.[rl_Indent5] = rl7.[rl_Indent5]
            AND rl8.[rl_Indent4] = rl7.[rl_Indent4]
            AND rl8.[rl_Indent3] = rl7.[rl_Indent3]
            AND rl8.[rl_Indent2] = rl7.[rl_Indent2]
            AND rl8.[rl_Indent1] = rl7.[rl_Indent1]
            AND rl8.[rl_Indent8] <> 0
            AND rl7.[rl_Indent9] = 0)

        LEFT JOIN ReportingLevels rl9
            ON (rl9.[rl_CompanyID] = @CompanyID
            AND rl9.[rl_Indent8] = rl8.[rl_Indent8]
            AND rl9.[rl_Indent7] = rl8.[rl_Indent7]
            AND rl9.[rl_Indent6] = rl8.[rl_Indent6]
            AND rl9.[rl_Indent5] = rl8.[rl_Indent5]
            AND rl9.[rl_Indent4] = rl8.[rl_Indent4]
            AND rl9.[rl_Indent3] = rl8.[rl_Indent3]
            AND rl9.[rl_Indent2] = rl8.[rl_Indent2]
            AND rl9.[rl_Indent1] = rl8.[rl_Indent1] 
            AND rl9.[rl_Indent9] <> 0
            AND rl9.[rl_Indent10] = 0)

    WHERE
        rl0.[rl_CompanyID] = @CompanyID
        AND rl0.[rl_Indent1] = 0);


INSERT INTO @result
SELECT * FROM @tmp1 
UNION 
SELECT * FROM @tmp2
ORDER BY [rl_DisplayName]

RETURN
END

Answer 1

Just Drop and Create your SQL statement

Answer 2

To change function type you must have to drop function and then only you can change function type by creating new type function.

There are three types of functions.

• Scalar
• Inline table valued
• Multi Statement

ALTER cannot be used to change the function type.

You need to DROP and CREATE the function

Answer 3

You're trying to use the syntax for a inline table-valued function when you actually have a multi-statement table-valued function. See the full description here.

You're using this:

--Transact-SQL Inline Table-Valued Function Syntax 
CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ =default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

BUT, you really want this:

--Transact-SQL Multistatement Table-valued Function Syntax
CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ =default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

Answer 4

Your header syntax for inline table valued functions

...
RETURNS TABLE 
AS
RETURN 
(
    SELECT .. FROM something WHERE ...
)
GO --end of function

You have a multi-valued table function.

...
RETURNS @result TABLE
(
CompanyName VARCHAR(300),
[rl_Index] INT,
[rl_Addr1] VARCHAR(MAX),
[rl_Addr2] VARCHAR(MAX),
[rl_DisplayName] VARCHAR(MAX)
);
AS 
BEGIN
...
Do this

Do that

Data into @result 

END --of function
GO

See MSDN for examples